Excel Forum

Arrange data for analysis with Pivot tables - split ranges of dates (years)?

Babsi W

New Member

Members

Forum Posts: 1

Member Since:
March 16, 2021

Offline

March 16, 2021 - 7:35 am

Dear Forum,

for a dataset of approx. 2000 members (selection to show data format attached) I would need to create a report showing how the numbers (total and among groups (unit/subunits)) and the distribution of nationalities, gender, etc. has evolved over the years.

My idea was to use pivots tables. However, I have problems to figure out how to organise the dates in order to get results with the pivot tables. For persons that held a membership before 2015 only year of entry and exit are given (i.e. without day/months).

Is it a solution to split these date ranges into single years to use them for the Pivot tables/charts? If so, what would be the procedure? (PowerQuery?).

Or is there a formula that can be used? If so, which?

Or, maybe, the data should be completely rearranged before starting any analysis?

Any ideas/solutions are very much appreciated!!

Best,

Babsi

Mynda Treacy

Admin

Forum Posts: 4449

Member Since:
July 16, 2010

Offline

March 16, 2021 - 4:42 pm

Hi Babsi,

Welcome to our forum!

You said that you want to see how the data evolves over the years, therefore you don't need to know the day or month the person joined and already have the Entry and Exit years in your data, so I don't see a need to do any further transformations of the data.

That said, I see that you don't have every year represented in the data and therefore, you can't use the setting in PivotTables field lists that allows you to 'show items with no data' allowing your charts to show an accurate timeline. To fix this you would need to use the Entry Date and Exit Date columns, and to do this you would need to fill the blank cells with a proper date. e.g. you could assume the entry date/exit dates were January 1st of the year.

You can do this with a formula that references the Entry (year) / Exit (year) columns e.g.

=DATE(Entry (year),1,1)

To quickly fill all the blank cells, select the cells in the Entry Date column > CTRL+G > Special > Blanks. With the cells selected, click in the formula bar and type the formula using the mouse to select the first cell with no date (in your example file it's H4) > CTRL+ENTER.

See file attached. Hope that points you in the right direction.

Mynda