Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question re incrementing Dates by one month
My apologies in advance if part of this question is off topic.
How do I use vba to increment a date by one month .... and (probably off topic) What is the usual practise for dating 'monthly' invoices when the date of previous invoice falls in the range 29th to 31st and the following month has less days Regards & TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question re incrementing Dates by one month
Here is one way that takes the last day of the month if that event happens.
Problem is, eventually it will always settle on the 28th Dim myDate As Date Dim newDate As Date myDate = "March 31 2005" If Month(DateSerial(Year(myDate), Month(myDate) + 2, 0)) < _ Month(DateSerial(Year(myDate), Month(myDate) + 1, Day(myDate))) Then newDate = DateSerial(Year(myDate), Month(myDate) + 2, 0) Else newDate = DateSerial(Year(myDate), Month(myDate) + 1, Day(myDate)) End If MsgBox newDate -- HTH Bob Phillips "SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message ... My apologies in advance if part of this question is off topic. How do I use vba to increment a date by one month .... and (probably off topic) What is the usual practise for dating 'monthly' invoices when the date of previous invoice falls in the range 29th to 31st and the following month has less days Regards & TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question re incrementing Dates by one month
On Fri, 6 May 2005 22:11:59 +0100, "SA3214" <sa3214<No
wrote: My apologies in advance if part of this question is off topic. How do I use vba to increment a date by one month .... and (probably off topic) What is the usual practise for dating 'monthly' invoices when the date of previous invoice falls in the range 29th to 31st and the following month has less days Regards & TIA I believe the usual practice is to date the invoice as of the last day of the month. In order to do this, you need to always refer back to a "seed date", or else, when adjusting to the end of the month, you'd have no way of knowing what the correct day should be for the following month. In VBA, the DateAdd function will adjust to the proper end of month date. So something like this should give you some ideas: Sub IncrMonth() Const SeedDt As Date = "31 Jan 2004" Dim NumMnths As Double For NumMnths = 0 To 12 MsgBox (NumMnths & " months since " & SeedDt & _ " is " & DateAdd("m", NumMnths, SeedDt)) Next NumMnths End Sub --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Incrementing dates in the format MMM-YY | Excel Discussion (Misc queries) | |||
Repeating/incrementing dates 35039 times | Excel Discussion (Misc queries) | |||
Dates Formatting Question - What if you do not know month and/or d | Excel Discussion (Misc queries) | |||
Fill column with dates of month depending on month in A1 | Excel Programming | |||
Incrementing dates by one hour returns bad result | Excel Programming |