need to auto fill day & date in column headings

March 31, 2014 at 19:11:27
Specs: Windows 7
I want to have 7 columns with headings Mon 14/4/2014 Tue 15/4/2014 etc through to Sun
with a vacant column between each main cell with no headings
I want to be able to alter the Monday date only and all cells will change to the correct day & dates across the spreadsheet automatically

message edited by ASFRon

See More: need to auto fill day & date in column headings

April 1, 2014 at 03:00:54
with a vacant column between each

Here is one way to do it:

In Column A enter you Monday Date as you normally would,
then for Tuesday, in column C, your next date column, enter the formula:


For Wednesday enter, in column E


For Thursday enter, in column G


etc. etc.

See how that works for you.


Report •

April 1, 2014 at 06:02:47
An additional option is to put this formula in A1, and then follow Mike's A1+1, A1+2, etc. suggestion:


This formula should always display the date of the Monday of the current week. It will automatically update when the sheet is opened and/or calculated. You can test it by changing the system date on your computer.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 1, 2014 at 22:04:22
Thanks again but that still does not work #Value! appears in H1 not the next day & date
I had it previously many years ago but can't get it to work now
Mon 14/1/2014 must all be in F1
The formula must then replicate the next day & Date in each cell across the top

Report •

Related Solutions

April 2, 2014 at 05:53:53
Since we can't see your spreadsheet from where we're sitting, it's hard to say exactly what is wrong, but I'll take a guess...

First, the date you used in Response #3 doesn't make sense: Mon 14/1/2014

If that date is supposed to be Monday, January 14, 2104, it is wrong. January 14, 2014 was a Tuesday. So let's assume you meant Mon 13/1/2014.

That said, if you are getting a #VALUE error when you try to add 1 to your "date" my guess is that the cell is not formatted as a Date and probably contains Text.

Here is what I did and it works just fine...

In F1 I entered this formula:


I formatted the cell as Custom ddd d/m/yyyy

This produced:

Mon 31/3/2014 since that is the Monday of the current week.

In H1 I entered =F1+1 and formatted it with the same Custom format. The formula returned Tue 1/4/2014.

I then selected H1:I1 and dragged it over to S1. This produced the dates for the rest of the current week, formatted as ddd d/m/yyyy

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 2, 2014 at 20:12:39
Hi Sorry about Day date not being correct it was a guess just to show an example

So to help you yes.
Mon 13/1/2014 would be in F1 I want the abbreviation of the day (Mon ) & the date to appear in the same cell F1

Then automatically the other cells listed change to Tue 14/1/2014, Wed 15/1/2014 etc through to Sunday ONCE the Mon date is changed every week.

I save as every roster as the Sunday date last day of week. as follows
WEnding Sun 19012014

I am trying not to use two rows
One for the day
the other for the dates which easily fill using the formula's I know & you are providing.

Thanks for the assistance

Report •

April 2, 2014 at 20:32:36

Report •

April 2, 2014 at 21:05:52

The auto change can not work on whenever it is opened just when the Monday date is altered for a new weeks roster.

Days remain constant in each cell but the dates will auto change wen Mondays date is entered.

I hope this is clear I have never tried using this type of help so apologise for any misunderstanding

Report •

April 3, 2014 at 04:13:31
If the auto update feature is not what you want, then just enter the date in F1 and format it as Custom...ddd d/m/yyyy

The =F1+1, =F1+2, etc. should still work fine. They do for me.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Ask Question