Created for a client who needed to use these as criteria in Excel queries (in fact a Microsoft Query, more about this later), this Excel worksheet shows Excel functions used to determine date ranges such as Monday of this week, last Monday, last Sunday, next Sunday, next Monday etc.

You can download this worksheet from the Office Web App link below or here: Date Ranges with Excel Functions

A quick guide to the functions used:
TODAY() – displays the current date.
WEEKDAY(serial_number, [return_type]) – returns a number which represents the day of the week e.g. Monday = 0. Change the return_type if you prefer Sunday to be 0. Very useful when you know that today’s weekday is 0 and adding 7 then means Sunday.
DATE(year, month, day) – returns a date from three different numbers e.g. DATE(2012,12,31) returns the last day of the year.
YEAR(serial_number) – returns the year for the date entered as a serial number.

Using Excel Functions to calculate dates and date ranges