Adding to Dates

[Guest]

I'm creating a benefits spreadsheet to help with health insurance eligibility
and COBRA notifications.

I want to write a formula that will look at the start date entered, add 90
days to it and then return a date that is the first day of the first month
following.

So if a date of 4/15/04 was entered, the formula would return 8/1/04.

 

[Anne Troy]

=DATE(YEAR(A1+90),MONTH(A1+90)+1,1)
**** Hope it helps! ****

~Dreamboat
Excel VBA Certification Coming Soon!
www.VBAExpress.com/training/
www.Brainbench.com Word Test Developer 2000,2002,2003
********************************

 

[Guest]

Hi,
Say the date is in A1
In another cell, use the formula:
=DATE(YEAR(A1+90),MONTH(A1+90)+1,1)
- A1+90 returns the date 90 days later
- DATE( year(@), Month(@)+1, 1) returns the date 1 month later and the 1st
of that month.

Regards,
Sebastien

 

[Trevor Shuttleworth]

Try this:

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

assuming the start date is in cell A5

Regards

Trevor

 

[Guest]

Thank you! It worked!!

Carole

=DATE(YEAR(A1+90),MONTH(A1+90)+1,1)
**** Hope it helps! ****

~Dreamboat
Excel VBA Certification Coming Soon!
www.VBAExpress.com/training/
www.Brainbench.com Word Test Developer 2000,2002,2003
********************************

 

[Tom Ogilvy]

Sub DDD()
Dim dt As Date, dt1 As Date
Dim dt2 As Date

For Each cell In Range("A1:A365")
cell.Value = DateValue("01/01/2004") + cell.row - 1
dt = cell.Value

dt1 = dt + 90
If Day(dt1) <> 1 Then
dt2 = DateSerial(Year(dt1), Month(dt1) + 1, 1)
Else
dt2 = dt1
End If
cell.Offset(0, 1).Value = dt2
Next
Range("C1:C365").Formula = "=B1-A1"
End Sub

a formula would be

=Date(year(A1+90),Month(A1+90)+1,1)

A question is what happends if date + 90 is on the first of a month?

 

[Myrna Larson]

With the start date in A1:

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

Note that with a start date of 1/2/2004, adding 90 days takes you to 4/1/2004.
The above formula returns 4/1/2004 rather than 5/1/2004. If the interval must
be at least 91 days, both of the following formulas return 5/1

=IF(DAY(A1+91)=1,A1+91,DATE(YEAR(A1+91),MONTH(A1+91)+1,1))
=DATE(YEAR(A1+90),MONTH(A1+90)+1,1)

I tried all 3 formulas with dates from 1/1/2004 through 12/31/2004. The only
differences involved cases where (StartDate + 90) falls on the 1st of a month.