Excel - add 6 months to a date 1

[jazminecat]

Hi all, I have an excel spreadsheet that calculates an employee's eligibility for vacation cashout. They are eligible for this cashout 6 months from their date of hire. However, this 6 months is calculated by the day of the month. For example, an employee hired on March 15 is eligible on Sept 15, but not Sept 14.

So, I need to calculate 6 months from their day of hire, based on the day of the month. To make things more complicated, I need to deal with the issue that arises if a month is short - for example, an employee hired on August 31 in theory would be eligible February 31, but yeah, there's no Feb 31, so they would be eligible 3 days after the 28th of February...

Does anyone have any thoughts on how to deal with this in a calculation? Is it even possible?

Thanks so much for your time.

 

[DirkStruan]

Jazminecat

Bit of a nasty definition to work, with but the following should work:

Assuming your date of joining is in cell A1,

"=(A1-EOMONTH(EDATE(A1,-1),0))+EDATE(EOMONTH(EDATE(A1,-1),0),6)"



Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[DirkStruan]

Oops:

Forgot to mention, to use the EDATE function you need the Analysis Toolpak add-in to be installed. See Tools\Add ins.... etc.

Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[jazminecat]

ooo, you are soo close! This works great...except for some reason 3/1/2006 as a start date returns 8/29/2006 instead of 9/1/2006.

 

[AnotherHiggins]

Dirk's formula will return Sept 12 based on March 15 - not what you wanted.

Try this:
[tab][COLOR=blue white]=EOMonth(A1,5)+Day(A1)[/color]

The logic says, "Give me the last day of the month, 5 months from the start date. Then add to that the day of the month from the start date."

So 03/15/06 = 08/31/06 + 15 = 09/15/06
[tab]and
08/31/05 = 01/31/06 + 31 = 03/03/06

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[DirkStruan]

Ok, February is screwing things up.

Try:

=(A1-(EOMONTH(EDATE(A1,-1),0)+1))+EDATE((EOMONTH(EDATE(A1,-1),0)+1),6)

(the +1 is making a beginning of month function)



Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[DirkStruan]

John

You've pointed out an obvious daft moment in that I blanked the point of adding months within the EOMonth function.

Jazminecat, use John's method as it's far more simple.

Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[jazminecat]

Thank you SO much y'all. It's brilliantly concise and so far, works flawlessly. Again, thank you.

 

[SkipVought]

and yet another...
[tt]
=date(Year(MyDate),Month(MyDate)+6,Day(MyDate))
[/tt]


Skip,

_[red][/red]