Calendar Dates

[Guest]

I have created a worksheet with references to calendar dates (this is a timesheet template). It worked fine for dates at the beginning of the month, but I then realized that where the start date is near the end of the month, it does not properly reflect the dates for the new month. So, for example, if the start date is Mar 28th, and the dates should be March 28, 29, 30, 31, then April 1, 2, 3, etc it shows March 28, 29, 30, 31, 32, 33... This looks quite absurd

Any suggestions on how to correct this

Thank

Don

 

[Norman Harker]

Hi Donald!

When adding months to create a series of months use:

=DATE(YEAR(A1),MONTH(A1)+1,MIN(DAY($A$1),DAY(DATE(YEAR(A1),MONTH(A1)+2
,0))))

Substitute A1 and $A$1 by the cell reference that contains your base
date and you should then be able to copy the formula down or across
and avoid the problem you have.

When adding days to a date use:

=DATE(YEAR(A1),MONTH(A1),DAY(A1)+1)

You'll find that after the number of days in the month exceeds the
number in that month, then the month will automatically click over.
This formula can be copied down and across to generate a series of
consecutive dates.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

I have created a worksheet with references to calendar dates (this

is a timesheet template). It worked fine for dates at the beginning
of the month, but I then realized that where the start date is near
the end of the month, it does not properly reflect the dates for the
new month. So, for example, if the start date is Mar 28th, and the
dates should be March 28, 29, 30, 31, then April 1, 2, 3, etc it shows
March 28, 29, 30, 31, 32, 33... This looks quite absurd.