Macro for Dates
 

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

Macro for Dates
 

On Tuesday, November 5, 2013 6:02:11 PM UTC-6, JCO wrote:
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 of days 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

Macro for Dates
 

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 :
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

Macro for Dates
 

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 :
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 :
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

Macro for Dates
 

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.

"Denis" wrote in message
...
On Tuesday, November 5, 2013 6:02:11 PM UTC-6, JCO wrote:
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 of days 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

Macro for Dates
 

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

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