Formula Help on Dates

Hello all!

If anyone can kindly assist me:

I am using a system generated report that spits out years/months someone has been with the company. I would like to pull this data for my pivot table, but am having trouble with the formula.

What I am trying to say (results should be in in Column O):

If N6 = If 0-4 years, then show me the year as a 1,2,3 or 4. Anything in the 0-11 months range should show as 1. If 5-9 years, then show me 5,6,7,8 or 9. etc. based on the following years of service ranges: (which will later be grouped in the pivot table).

0-4
5-9
10-14
15-19
20-24
>25

I would like to use one spreadsheet moving forward, where I can just plug in the data each month and refresh the tables. I would cap the number of lines in my table at 700. Will this mess anything up if there are blank cells?

Thanks!!!

See attached:

Added calculation is Column L and O of "Raw Data"

IF([@[PERSONAL Birthdate]]="","",DATEDIF([@[PERSONAL Birthdate]],TODAY(),"y"))

=IF([@[STATUS Most Recent Hire Date]]="","",DATEDIF([@[STATUS Most Recent Hire Date]],TODAY(),"y"))

in Sheet1

D17

=IF([@[STATUS Most Recent Hire Date]]="","",DATEDIF([@[STATUS Most Recent Hire Date]],TODAY(),"y"))

Copy across to H and down to row 21

in D22

=COUNTIFS('RAW DATA'!$O:$O,">=" &Sheet1!$C22,'RAW DATA'!$L:$L,">=" &Sheet1!D$16,'RAW DATA'!$L:$L,"<" &Sheet1!E$16)

Copy across to H

in I22

=COUNTIFS('RAW DATA'!$O:$O,">=" &Sheet1!$C22,'RAW DATA'!$L:$L,">=" &Sheet1!I$16)


You can use similar formulae for your "Gender" table

Updated file with "Gender" table.

in D28

=COUNTIFS('RAW DATA'!$M:$M,"=" &Sheet1!$C28,'RAW DATA'!$L:$L,">=" &Sheet1!D$16,'RAW DATA'!$L:$L,"<" &Sheet1!E$16)

Copy across to H and Down

in I28

=COUNTIFS('RAW DATA'!$M:$M,"="&Sheet1!$C28,'RAW DATA'!$L:$L,">="&Sheet1!I$16)

copy down

Amazing John! Thank you!

I see a number of #VALUE errors on my age calculations per the sheet you sent...do you know why / how to resolve?

Thanks

There are no #VALUE errors in the file I posted.

Hmmm - I will re-attach to see if you see the error or not. I also took a screenshot.