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.

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

"turboalto" wrote:

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.

Try this:

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

assuming the start date is in cell A5

Regards

Trevor


"turboalto" wrote in message
...
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.

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?

--
Regards,
Tom Ogilvy




"turboalto" wrote in message
...
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.

With the start date in A1:

=IF(DAY(A1+90)=1,A1+90,DATE(YEAR(A1+90),MONTH(A1+9 0)+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+9 1)+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.

On Thu, 30 Sep 2004 10:49:04 -0700, turboalto
wrote:

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.