Macro for Dates

[JCO]

I have a report that requires the current date in the right header and the
date range in left header. The date range is the first day of the previous
month to the last day of the previous month.

Example: Today is 11/05/2013. As I run the macro, it should look like
below:
Date Range: 10/01/2013 - 10/31/2013 Date:
11/05/2013

Thanks

 

[Denis]

I have a report that requires the current date in the right header and the

date range in left header. The date range is the first day of the previous

month to the last day of the previous month.



Example: Today is 11/05/2013. As I run the macro, it should look like

below:

Date Range: 10/01/2013 - 10/31/2013 Date:

11/05/2013



Thanks

Well, first you get today's date (11/5/2013) with the TODAY() function as the formula in your rightmost cell. Then set a Date variable to this cell value and take advantage of the fact that you can do date arithmetic. Subtract the number of days in the previous month (31 for Oct) and the number ofdays in the current date less one (-31-5+1) to get the start of the previous month. Then add the number of days in the previous month less one (+31-1) to get the ending date. Use the Format function to format the starting and ending dates the way you want them to look. Only tricky thing here is you have to handle leap years to determine whether 28 or 29 days in February.

Denis

 

[isabelle]

hi JCO,

Dim firstDay As Date, lastDay As Date

firstDay = DateSerial(Year(Now), Month(Now) - 1, 1)
lastDay = DateSerial(Year(Now), Month(Now) - 1,
Day(DateSerial(Year(Now), Month(Now), 1 - 1)))

With ActiveSheet.PageSetup
.LeftHeader = "Date Range: " & firstDay & " - " & lastDay
End With

isabelle

Le 2013-11-05 19:02, JCO a écrit :

 

[isabelle]

to adjust date format in different language

Dim FirstDay As Date, LastDay As Date
Dim sFirstDay As String, sLastDay As String

FirstDay = DateSerial(Year(Now), Month(Now) - 1, 1)
LastDay = DateSerial(Year(Now), Month(Now) - 1,
Day(DateSerial(Year(Now), Month(Now), 1 - 1)))

sFirstDay = Format(FirstDay, "mm/dd/yyyy")
sLastDay = Format(LastDay, "mm/dd/yyyy")

With ActiveSheet.PageSetup
.LeftHeader = "Date Range: " & sFirstDay & " - " & sLastDay
End With

isabelle

Le 2013-11-06 18:26, isabelle a écrit :

 

[JCO]

Thanks, I did figure out the code yesterday. I'm sure I used more code than
required but it does work (including for leap year.

 

[JCO]

Thanks,
Your version is much less code than I used. I was not familiar with the
"DateSerial".
I will review it more later