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