I need to display a date in a field, this needs to default to the
first of the nearest month, 60 days from todays date.

So in other words for today it should return 01/12/07 (DD/MM/YY).

How would this be done using Excel VBA please?

Thanks!

Is nearest month for Jan 16th Jan or Feb?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"David" wrote in message
ups.com...
I need to display a date in a field, this needs to default to the
first of the nearest month, 60 days from todays date.

So in other words for today it should return 01/12/07 (DD/MM/YY).

How would this be done using Excel VBA please?

Thanks!

Here is a start:

Note: I am in the US so these are US format.

dt = dateSerial(year(date),Month(date),day(date)+60)
? dateSerial(year(dt),month(dt),1)
11/01/2007


you can get the first day of the next month with

dt = dateSerial(year(date),Month(date),day(date)+60)
? dateSerial(year(dt),month(dt)+1,1)
12/01/2007

Beyond that, it depends on what you mean by nearest.

format(dateSerial(year(dt),month(dt)+1,1),"dd/mm/yy")
will give you the format you describe.

--
Regards,
Tom Ogilvy


"David" wrote:

I need to display a date in a field, this needs to default to the
first of the nearest month, 60 days from todays date.

So in other words for today it should return 01/12/07 (DD/MM/YY).

How would this be done using Excel VBA please?

Thanks!

Thanks all, it wasn't as hard as I thought at first, this works:

' Prepopulate the data for the first of the month, plus 60 days
hence.
Today = Date
' Now add 60 days
NextDate = DateAdd("d", 60, Today)
Dayte = Day(NextDate)

If (Dayte = 1) Then
' do nothing, it's the right day
dFinalDate = dDayte
Else
' Need to find the 1st of the following month
Dayte = Dayte - 1
dFinalDate = DateAdd("m", 1, NextDate)
dFinalDate = DateAdd("d", -Dayte, dFinalDate)
End If

Range("E8").Value = dFinalDate

By all means comment if you see any problems or can make it more
efficient, thanks again!