Autofill days across one row, skipping every other column as its hidden.

[variantquery91]

Hi all,

New to the site, would be great if you could help with a work issue.

Having issue auto filling days, as there is a hidden column between each column for a excel work sheet.

So when I try to auto fill I end up seeing every other day rather then the next day.

How I want to work for example is beginning with C1 Saturday, then E1 to say Sunday rinse and repeat for a whole years worth.

If anyone could please help with a formula that can manage this, I would be very grateful.

Many thanks.

[AliGW]

Welcome to the forum.

These day labels - presumably they are dates formatted as days?

If so, you could try this:

=C1+0.5

and drag copy across.

[variantquery91]

Hi Ali,

Thanks for your reply.

Sadly that did not work as I am using text words not dates. Such as Sat, Sun, Mon etc..

I have the dates below working on number 2 row, e.g. 01/06/2019 etc, it's the text format above for worded days I have the issue with.

[AliGW]

OK - so change the entry in C1 to a date (say 01/06/2019). Then go to the cell formatting drop-down on the Home ribbon and select Custom - set the formatting to dddd. Now you have a day.

Next, in D1 add my formula and drag copy across - you should get a repeat of the first day in the hidden cells and the correct new day in those that are not hidden.

Using date formatting rather than text is more flexible for you anyway.

Excel 2016 (Windows) 32 bit

	E	F	G	H	I	J	K	L	M
9	Friday	Friday	Saturday	Saturday	Sunday	Sunday	Monday	Monday	Tuesday

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	E	F
9	=TODAY()	=E9+0.5

Sheet: Sheet1

[AliGW]

The other way to do it, of course, is this (in C1):

=TEXT(WEEKDAY(C2),"dddd")

and drag copy across. HOWEVER, that leaves you with just text and NOT a recognisable date.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[variantquery91]

Thank you so much works great.

My only niggle is now it display the full text day e.g. Monday instead of just Mon, which has created an issue of a lot of the columns not showing the date because the box is width of the box is too small for the text.

When I try to highlight the entire page to spread the width out it unhides all of my hidden columns, which creates another long job of hiding.

[AliGW]

OK - no problem. Change the formatting to ddd (instead of dddd).

dd = number of the day of the week

ddd = three-letter day - Mon, Tue, Wed, etc.

dddd = full name - Monday, Tuesday, Wednesday, etc.

[variantquery91]

Fantastic.

Thank you so much!

[AliGW]

Ta-da! Glad we got there.