Calculate number of months between start of fiscal year and anniversary month/day?

[Smokeyham]

Hi,

I have a list of start dates for employees (Month/Day/Year). I would like to calculate the difference in months between the month/day in which they were hired and July 1st.

For instance, for someone hired August 15, 2010 the difference between July 1 and August 15 would be about 1.5 months. so that is how long they have worked in that fiscal year before their anniversary month/day

I'm trying to figure the following out:

1. How to extract the Month and day (without the year) so they can be used in a formula.

2. Once extracted, how do I tell Excel that someone hired on January 1st will have worked 6.1 months in the fiscal year while someone hired October 1st will have worked only 3.1 months into the fiscal year before their anniversary.

Any help would be most welcome.

Thanks!

Gordon

 

[Michael M]

Maybe this
Will give you Months and days

=DATEDIF(A1,B1,"YM") & "." & DATEDIF(A1,B1,"MD")

 

[Smokeyham]

Maybe this
Will give you Months and days

=DATEDIF(A1,B1,"YM") & "." & DATEDIF(A1,B1,"MD")

Thanks so much for the response!

Below are the results I am getting, and the results as they should be. I'm thinking there must be a way to compare this against 12 months to get to the correct answer, but I am not seeing it. I did try reversing the columns, but just go a "#num" error message.

The "Approximate Value column (I) are the values which I should be getting (I estimated these by hand).

Excel Formula Month Day

 

[Michael M]

you will need to include the DATEDIF for the years as well

=DATEDIF(A1,B1,"Y") & "." & DATEDIF(A1,B1,"YM") & "." & DATEDIF(A1,B1,"MD")

 

[Smokeyham]

you will need to include the DATEDIF for the years as well

=DATEDIF(A1,B1,"Y") & "." & DATEDIF(A1,B1,"YM") & "." & DATEDIF(A1,B1,"MD")

Michael,

Thanks for the continued help. That adds the year, but I am really just trying to calculate the number of months (in decimal format) which the anniversary month/day is from July 1st. For instance a January 15th anniversary-of-hire date is 7.5 months from July 1st.

 

[B___P]

Strange issue in excel 2010 italian version, i did not find date.diff but filling cell with formula I understand excel recognizes it! But no idea about parameters.

I tried to solve with

=IF(MONTH(A1)>MONTH(B1),YEARFRAC(B1,DATE(YEAR(B1),MONTH(A1),DAY(A1)),0)*12,12-YEARFRAC(B1,DATE(YEAR(B1),MONTH(A1),DAY(A1)),0)*12)

Perhaps another localised issue in the order of year, month and day inside date(). Try to rearrange it.
BTW 0 in yearfrac is the base for calculation, you may change it to fit better your needs.

From July 1st to January 15th there are 6 months and a half...
bye

 

[Smokeyham]

Strange issue in excel 2010 italian version, i did not find date.diff but filling cell with formula I understand excel recognizes it! But no idea about parameters.

I tried to solve with

=IF(MONTH(A1)>MONTH(B1),YEARFRAC(B1,DATE(YEAR(B1),MONTH(A1),DAY(A1)),0)*12,12-YEARFRAC(B1,DATE(YEAR(B1),MONTH(A1),DAY(A1)),0)*12)

Perhaps another localised issue in the order of year, month and day inside date(). Try to rearrange it.
BTW 0 in yearfrac is the base for calculation, you may change it to fit better your needs.

From July 1st to January 15th there are 6 months and a half...
bye

That works perfectly!!! Thanks so very much!

Gordon