# Thread: Total Months (Excel 2003)

1. ## Total Months (Excel 2003)

Hi,

I am using a simple formula find out the total months the employee has been in the system.=IF(A2<DATE(2006,10,1),12,DATEDIF(A2,DATE(2 007,9,30),"M"))

However i have observed that by using the formula, i am getting one month short. for eg, if someone has joined on 01-Dec-06 and on 30-Sept-07 should be 12 Months. The output is 11 Months.

Can someone explian who so. Also if the formula can be modified to check if the Date of Joining is <15th take the entire month else next month.

Attaching the file for ur reference.

Regard
Baiju

2. ## Re: Total Months (Excel 2003)

DATEDIF subtracts months. It counts 5 months from 4/1/2007 to 9/1/2007 (or 9/30/2007) since 9 - 4 = 5. If you want to count this period as 6 months, you have to add 1 to the result. (Your example is incorrect, by the way - from 12/1/2006 to 9/30/2007 is 10 months, not 12).

You could use this formula to take into account whether the day is before or after the 15th:

=IF(A2<DATE(2006,10,1),12,DATEDIF(A2-DAY(A2)+1+14*(DAY(A2)>=15),DATE(2007,10,1),"M"))

3. ## Re: Total Months (Excel 2003)

Hi Baiju

From my understanding datedif using M for month, as in your example calculates the number of complete months between Date 1 and Date 2. Your formula is therefore doing the correct thing and counting the months from the end of December. ie Jan through to September

4. ## Re: Total Months (Excel 2003)

Or perhaps:

=MIN(12,DATEDIF(A2,DATE(2007,10,0),"M")+(DAY(A2)<1 5)*1)

though I'm not sure it fits all your possible scenarios.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•