Yearly Roster - Day count to date.

If you put 1/1/2018 in a cell and drag it down or across, Excel will automatically increment the days. When it gets to 1/31/2018, the next will be 2/1/2018, etc. If you want to see the day name, then custom format:
dddd, dd/mm/yyyy. dddd being the day name.

Originally Posted by protonLeah

If you put 1/1/2018 in a cell and drag it down or across, Excel will automatically increment the days. When it gets to 1/31/2018, the next will be 2/1/2018, etc. If you want to see the day name, then custom format:
dddd, dd/mm/yyyy. dddd being the day name.

Edit* ignore this comment. I just re-read it properly. give me a second to trial this out.


Edit ** Is there a formula which allows me to change the YEAR.
So assumed Cell A1=Changeable year... Could i perhaps do =DATE($A$1,1,1) , =DATE($A$1,1,1+1).
Based on that example. it doesn't seem to work that way :S

With the year in A1, yes, use =DATE($A$1,1,1) in some cell (A2?). Then in the cells below use =A2+1 and drag this one down

In other words, how do i make my Month headers (february) extend 1 more column based on the extra day .

I believe manipulating the physical size of ranges is going to require VBA ... which I do not know enough about.

The only formula wise approach that I can think of is to commit 29 columns to February and fill in the 29th only on leap years. Also include a helper cell (or named formula in Name Manager) that calculates leap years. The leap year formula ... in D2(?) could be

Formula:

Please Login or Register  to view this content.

returns 1 for leap years 0 if not.

If statically "stretching" the February merged cell is acceptable I still see challenges ahead that also include a work around for the merged cells. Merged cells almost always cause havoc for formulas.

If this is acceptable please let us know.

Hello aileenbarcelon. Welcome to the forum.

To access the file (download it) just click on the link in post #5. Follow the prompts and the workbook will open in Excel.

In the event you wish to post a solution please take note that this thread is just over 3 years old and there has been no activity in it for about the same. It's not likely the thread owner is going to return to see it.

Again welcome aboard.