Enter first date of the year only and will populate the rest of the months

Hi,

I was trying to create a calendar with the months located on separate sheets.

I want the connection by just entering the first day of the year and it will populate the whole year.

Connecting January and February is ok but the problem I'm having is when March is connected to February due to the year if it falls on a leap year or not leap year.

If it's not leap year then the connection is blank so March dates are all error, if it falls on a leap year then its ok.

Please see my attached sample on what I'm trying to do.

Also, the Week number shows error if the leap year date is blank in February.

Thanks in advance.

You can use this in E5 of the Feb sheet:

=MAX(Jan!$5:$5)+1

and similar for the other sheets, each looking back to the previous sheet.

Hope this helps.

Pete

Try this formula in cell E5 of the worksheet "Mar":

=MAX(Feb!AF5:AG5)+1

The calendar is done for you with array formulas, so you just need to change the year in Jan!D3 that all will adjust.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

File attaches

Hi Everyone,

Thank you very much for the help.

I use Pete_UK and Phuocam formula and it works.

Thank you very much.

Good to hear it - thanks for the rep.

Pete