How to advance the NOW or TODAY funtion

[AussieExcelUser]

I would like to be able to format the TODAY or NOW functions so that they return a future date value. I have mucked around with things like NOW+1 but to no avail.

The format I am using is mmm-yy and instead of returning October today, I want the function to return November (next month).

Any ideas would be appreciated.

Thanks.

[Bryan Hessey]

I would like to be able to format the TODAY or NOW functions so that they return a future date value. I have mucked around with things like NOW+1 but to no avail.

The format I am using is mmm-yy and instead of returning October today, I want the function to return November (next month).

Any ideas would be appreciated.

Thanks.

=DATE(YEAR(NOW()),11,1)

will get you 1/Nov/2006, format the cell to suit

hth
---

[AussieExcelUser]

Hello again Bryan,

I'll have to get you on the pay roll with all of the assistance you provide me

Unfortunately your solution is not what I am after, have a look at the script below, basically I have an auto save occurring when a macro is run and I need to have the following month in the file name. The spreadsheet is a production plan that is worked out in the current month for the following month. That is why I am after some way to have the date automatically advance to the next month. The users of this plan cannot be relied upon to save it as the correct month and that is critical for it to be loaded into our MRP software correctly.

Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "mmm-yy")
Set wb = ActiveWorkbook
With wb
.SaveAs Sheets("Ts Prod Plan").Range("A2").Value _
& " Production Plan " & strdate & ".xls"
'wb.Close False
End With

Now I'm off to translate your latin signature line!

Cheers,

Grant.

[Bryan Hessey]

Hello again Bryan,

I'll have to get you on the pay roll with all of the assistance you provide me

Unfortunately your solution is not what I am after, have a look at the script below, basically I have an auto save occurring when a macro is run and I need to have the following month in the file name. The spreadsheet is a production plan that is worked out in the current month for the following month. That is why I am after some way to have the date automatically advance to the next month. The users of this plan cannot be relied upon to save it as the correct month and that is critical for it to be loaded into our MRP software correctly.

Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "mmm-yy")
Set wb = ActiveWorkbook
With wb
.SaveAs Sheets("Ts Prod Plan").Range("A2").Value _
& " Production Plan " & strdate & ".xls"
'wb.Close False
End With

Now I'm off to translate your latin signature line!

Cheers,

Grant.

So would

strdate = "Nov-" & Format(Now, "yy")

or

strdate = Format(Now + 30, "mmm") & "-" & Format(Now + 30, "yy")

added
except on the first or last days of the month, perhaps you could add +days-per-thismonth instead of 30 to be accurate.


help?
---

[AussieExcelUser]

I went with your second suggestion (strdate = Format(Now + 30, "mmm") & "-" & Format(Now + 30, "yy")) which will work a treat as the report is always generated around the middle of the month (it has to be completed by the 3rd week without fail) so the +30 will always suffice.

Thanks again, your help is much appreciated.

Grant

[Bryan Hessey]

I went with your second suggestion (strdate = Format(Now + 30, "mmm") & "-" & Format(Now + 30, "yy")) which will work a treat as the report is always generated around the middle of the month (it has to be completed by the 3rd week without fail) so the +30 will always suffice.

Thanks again, your help is much appreciated.

Grant

Good to see it works for you, and thanks for the response.

---