1st of month formula help 2

[bpenney]

Hopefully someone can help me out. What I want to do is return a date in cell B1 based on the date input in cell A1.

What I would like the formula to do is to add 90 days to the date in cell A1 and then return, as an answer, the first of the next month.

What I want to use this for is benefit effective dates for our employees. So if you were to enter a date of 6/13/04 in cell A1 I want cell B1 to show 10/1/04.

Is this possible?

TIA

 

[bluedragon2]

=DATE(YEAR(A1+90),MONTH(A1+120),1)



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[cLFlaVA]

=IF(MONTH(F1)<>12,DATE(YEAR(F1),MONTH(F1)+1,1),DATE(YEAR(F1)+1,1,1))



*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[bpenney]

Thanks Blue,

Worked like a charm.

cLFlaVA, thanks for the input also.

 

[cLFlaVA]

blue-

won't your solution cause a problem if, in 90 days from now, the month is December? This will give you the value of 01/01/2004, when it should be 01/01/2005.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[bluedragon2]

no cL,

The year value is increased by 90 also. If the date is 11/11/2004, the year value +90 days will result in 2005.

Actually, the month portion would work for all dates, but should be put as:

=DATE(YEAR(A1+90),MONTH(A1+90)+1,1)

Just for a true result of the problem.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[cLFlaVA]

blue-

As your code is posted,

=DATE(YEAR(A1+90),MONTH(A1+120),1)

will cause a value of 9/1/2004 in cell A1 to show as 12/1/2004, unless my Excel is calculating incorrectly.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[cLFlaVA]

Additionally, 9/25/2004 returns 1/1/2004.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[bluedragon2]

That is what it should be according to the posters criteria...

9/1/2004 + 90 days is 11/30/2004

add 1 month and your result should be 12/1/2004

9/25/2004 + 90 days is in the 12th month and the result should be 1/1/2005

That is why he should use:

=DATE(YEAR(A1+90),MONTH(A1+90)+1,1) for the latter



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Hi,

Simply...
[tt]
=DATE(YEAR(A1+90),MONTH(A1+90)+1,1)
[/tt]


Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244