Discover / Find Out The First Or Last Day Of Week For Any Date in MS Excel

Discover / Find out The First Or Last Day Of Week For Any Date in Microsoft Excel with Example.

Sometimes you may need to Discover / Find out what the first day of a week is by looking at some other date.

If we have August 20, 2014 in cell A2, we can use this formula to Discover / Find out that the first day of the same week is August 17, 2014 (assuming you want to treat Sunday as the first day of the week).

=A2-WEEKDAY(A2,1)+1

Weekday Function Excel

As you may know, when you type a date in MS Excel, it is converted into a serial number that represents the number of elapsed days since January 1, 1900. For example, when you type August 20, 2014 into your excel spreadsheet, Excel converts that date into the serial number 41871, even though it may be actually displayed on your worksheet as a date format. Change the number format of any date to General and you will see its serial equivalent.

When a formula is referencing August 20, 2014 in cell A2, Excel convert this date and look like 41871.

The second part of this formula uses the WEEKDAY function.

WEEKDAY(serial_number,[return_type])

The WEEKDAY function takes two arguments. The first option is serial_number which can be a date or a reference to a date. The second argument [return_type] is optional and therefore can be omitted. In it's basic form, the WEEKDAY function takes a date returns a number that represents the day of the week (e.g. Sunday=1, Monday=2, etc...). If you want to have Excel treat Monday as the first day of the week, you will need to use 2 for the return_type. A return_type equal to 2 uses Monday=1, Tuesday=2, etc...

Understanding the above information, we can now see how this formula woks.

1) First it takes the referenced date in cell A2 and converts it to its serial number equivalent (41871);

2) The WEEKDAY function takes the referenced date (Wednesday, August 20, 2014) and Discover / Find outs its day of the week. In this case, it is the fourth day of the week (using the default assumption of Sunday being the first day of the week).

3) When the weekday value (4) is subtracted from the referenced date (41871), the result is the day prior to the first day of the week, August 16, 2014 (41867).

4) Adding 1 returns the first day of the week August 17, 2014 (41868). It's a little tricky but it works.

Discover / Find out Date for First Day of a Work Week (assuming Monday):

If you are dealing with workdays, with Monday being the first workday, you can modify this formula to return Monday, August 18, 2014 as the first workday of the week that contains August 20, 2014, by using 3 as the return_type in the WEEKDAY function.

=A2-WEEKDAY(A2,2)+1

Weekday Function Excel

Discover / Find out Date for Last Day of a Work Week (assuming Friday):

Assuming Friday is the last day of your work week, you can use this formula to return the date of the last day of the work week for any given date

Weekday Function Excel