Create a non standard weekly date range autofill in excel

1

I am working on a monthly spreadsheet of our daily takings.
I need one line for a sunday, monday and tuesday but two lines for wednesday, thursday, friday and saturday. I can't seem to hit the magic formula to copy this down the column.

Wed 1/Oct/2014
Wed 1/Oct/2014
Thur 2/Oct/2014
Thur 2/Oct/2014
Fri 3/Oct/2014
Fri 3/Oct/2014
Sat 4/Oct/2014
Sat 4/Oct/2014
Sun 5/Oct/2014
Mon 6/Oct/2014
Tue 7/Oct/2014

I am sure there is a simple answer but I am stumped.

Vic

Posted 2014-09-25T12:53:28.837

Reputation: 13

Its for a monthly spreadsheet of cash takings for our business. It's quiet on sun/mon/tues so there is only 1 cash register opened. The other days there are two cash registers opened so need two lines for these days. Sorry I don't understand algorithms – Vic – 2014-09-25T13:17:53.327

You need a line for the 10th of each month, except when it falls on Wed - Sat, in which case you need 2 lines? ah nevermind, this is dd/mm/yy - sorry. – Raystafarian – 2014-09-25T13:17:57.107

Oops - confusion may arise in my original post it's meant to be - 1 Oct 2014, 2 Oct 2014, etc In Australia dd/mm/yy. – Vic – 2014-09-25T13:21:02.497

Answers

0

You could also use the weekday function which will give you what day of the week it is. The default format is 1=Sunday through 7=Saturday

In A3

=IF(WEEKDAY(A2)>=4,A2+0.5,A2+1)

If the day of the week is Wednesday through saturday add half a day otherwise add a whole day. With no time shown it doesn't matter if the time is 0:00 or 12:00 which is what adding 0.5 effectively does.

If time matters you would have to look at the two rows above which is less ideal.

gtwebb

Posted 2014-09-25T12:53:28.837

Reputation: 2 852

Thanks very much for your assistance. Works great ... AND saves me soooo much time! Cheers – Vic – 2014-09-26T00:42:35.603

Asked: 2014-09-25T12:53:28.837

Viewed: 82 times

Active: 2014-09-25T15:28:27.960