Microsoft Excel – creating a work week series based on a Monday to Saturday working week

If you type in a date in Microsoft Excel and drag down, the subsequent cells will show subsequent dates and when you look at the Autofill options, you will see options for selecting for filling Days or Weekdays.

Date Autofill options

However, for some people they work Mondays to Saturdays.

To create a series based on a six day week with no Sunday working, type the first date into the relevant cell, format all required cells as Date, then in next cell down, type the formula

=IF(WEEKDAY(A1)=7, A1+2, A1+1)

where A1 can be changed to whatever cell the first date is in.

Using the fill handle, you can then copy down as required.

This will then create a series which includes Saturdays but not Sundays.

Find out more about our Microsoft Excel training Sydney, Australia and our Microsoft Excel training London, Surrey, Middlesex or our Microsoft Excel courses Dorset, Hampshire, Wiltshire, UK.


About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s