ThreeWave Date Functions

This page describes a number of Excel formulas for working with dates.
ShortFadeBar

Date Functions

Many, if not most, worksheets use dates in one manner or another. This page describes a number of worksheet functions related to dates. The downloadable workbook contains all of these functions as well as VBA functions that perform the same tasks.

Many of these formulas are array formulas and must be entered with CTRL SHIFT ENTER rather than just ENTER. They will not work correctly if you do not use CTRL SHIFT ENTER. See the array formulas page for an in-depth discussion of array formulas and how to use them.

download You can download the file with all the example formulas and code on this page.

Is A Year A Leap Year?

The following formula tests whether the year in B11 is a leap year and returns either TRUE or FALSE.

=DAY(DATE(B11,2,29))=29

Day Of Year

The following formula returns the current day of the year in A1.

=A1-DATE(YEAR(A1),1,1)+1

Days In A Month

The following formula returns the number of days in the month in C16 and year in B16. This works because the 0th day of the next month is the last day of the current month.

=DAY(DATE(B16,C16+1,0))

First Day Of Month

The following formula returns the date of the first day of the month using the year in B21 and month in C21.

=DATE(B21,C21,1)

Last Day Of Month

The following formula returns the date of the last day of the month using the year in B27 and month in C27. This works because the 0th day of the next month is the last day of the current month.

=DATE(B27,C27+1,0)

First Weekday Of Month

The following formula returns the first weekday (Monday - Friday) of the month where cell B84 is the year and C84 is the month. Change the values within the CHOOSE function if your work week is other than Monday through Friday. The values in the CHOOSE function are the number of days to add to the date based on the weekday. So, the first 1 in the CHOOSE list indicates that for Sunday, add one day. For Monday - Friday, add 0 days. For Saturday, add 2 days. Adjust these values to fit your work week.

=DATE(B84,C84,1)+CHOOSE(WEEKDAY(DATE(B84,C84,1)),1,0,0,0,0,0,2)

Last Weekday Of Month

The following formula returns the last weekday (Monday - Friday) of the month where cell B89 is the year and C89 is the month. Change the values within the CHOOSE function if your work week is other than Monday through Friday. The values in the CHOOSE function are the number of days to add to the date based on the weekday. So, the first 1 in the CHOOSE list indicates that for Sunday, add one day. For Monday - Friday, add 0 days. For Saturday, add 2 days. Adjust these values to fit your work week.

=DATE(B89,C89+1,0)-CHOOSE(WEEKDAY(DATE(B89,C89+1,0)),2,0,0,0,0,0,1)

Count Of Day-Of-Week In Period

The following array formula counts the number of day of week in an interval. The start date is in B33, the end date is in C33 and the day of the week (1 = Sunday, 2 = Monday, ..., 7 = Saturday) is in D33. For example, you can calculate the number of Mondays between 15-Janary-2011 and 15-April-2011 (equals 13).

=SUM(--(WEEKDAY(ROW(INDIRECT(WEEKDAY(B33)&":"&C33-B33+WEEKDAY(B33))))=D33))

Count Of Day-Of-Week In A Month

The following array formula counts the number of days of the week in one month. With any date of the month in question in cell B38 and the day of week (1 = Sunday, 2 = Monday, ..., 7 = Saturday) in C38. For example, you can calculate the number of Mondays in January-2011 (equals 5).

=SUM(--(WEEKDAY(ROW(INDIRECT(WEEKDAY(DATE(YEAR(B38),MONTH(B38),1))&
":"&DATE(YEAR(B38),MONTH(B38)+1,0)-DATE(YEAR(B38),MONTH(B38),1)+WEEKDAY(DATE(YEAR(B38),MONTH(B38),1)))))=C38))


Nth Day Of Week In A Month

The following array formula will return the Nth (e.g. first, second, etc) day of week (1 = Sunday, 2 = Monday, ..., 7 = Saturday) for the year specified in cell B43, the month in C43, day of the week in D43, and Nth in E43. For example, you can find the date of the 3rd Monday of January-2011 (equals 17-Jan-2011).

=DATE(B43,C43,1+((E43-(D43>=WEEKDAY(DATE(B43,C43,1))))*7)+(D43-WEEKDAY(DATE(B43,C43,1))))

Nth Day Of Week In A Year

The following array formula will return the Nth (e.g. first, second, etc) day of week (1 = Sunday, 2 = Monday, ..., 7 = Saturday) for the year specified in cell B48, the day of week in C48, and Nth in D43. For example, you can find the date of the 10th Monday of 2011 (equals 7-March-2011).

=DATE(B48,1,1+(D48-(C48>=WEEKDAY(DATE(B48,1,1))))*7)+C48-WEEKDAY(DATE(B48,1,1))

Next Nth Day Of Week

The following formula will return the date of the next Nth day of week (Sunday = 1, Monday = 2, ..., 7 = Saturday) from the start date. The start date is specified in cell B53, the N in C53, and day of week in D53. For example, you can find the date of the 6th Monday following 14-January-2011 (equals 21-February-2011).

=B53+D53-WEEKDAY(B53)+(7*(D53<WEEKDAY(B53)))+(7*(C53-1))

Previous Nth Day Of Week

The following formula will return the date of the previous Nth day of week (Sunday = 1, Monday = 2, ..., 7 = Saturday) from the start date. The start date is specified in cell B59, the N in C59, and day of week in D59. For example, you can find the date of the 6th Monday before 14-January-2011 (equals 6-December-2010).

=B59+D59-WEEKDAY(B59)+(7*(D59<WEEKDAY(B59)))-(7*C59)

Next Workday Following A Date

The following array formula returns the next workday (Monday - Friday) following a date. The start date is in cell B64. Change the values in the CHOOSE function if your work week is something other than Monday through Friday.

=B64+CHOOSE(WEEKDAY(B64),1,0,0,0,0,0,2)

Previous Workday Following A Date

The following formula returns the previous workday (Monday - Friday) before a date. The start date is in cell B69. Change the values in the CHOOSE function if your work week is something other than Monday through Friday.

=B69-CHOOSE(WEEKDAY(B69),2,0,0,0,0,0,1)

First Day Of Week In A Month And Year

The following formula returns the date of the first day of week in a month and year. B94 contains the year, C94 contains the month, and D94 contains the day of week (Sunday = 1, Monday = 2, ..., 7 = Saturday). For example, the first Thursday in February, 2011 is 3-February-2011.

=DATE(B94,C94,1+((1-(D94>=WEEKDAY(DATE(B94,C94,1))))*7)+(D94-WEEKDAY(DATE(B94,C94,1))))

Last Day Of Week In A Month And Year

The following formula returns the date of the last day of week in a month and year. B100 contains the year, C100 contains the month, and D100 contains the day of week (Sunday = 1, Monday = 2, ..., 7 = Saturday). For example, the last Thursday in February, 2011 is 24-February-2011.

=DATE(B100,C100,1+((1-(D100>=WEEKDAY(DATE(B100,C100,1))))*7)+ (D100-WEEKDAY(DATE(B100,C100,1))))+(7*(SUM(--(WEEKDAY(ROW(INDIRECT("1:"&DATE(B100,C100+1,0)-DATE(B100,C100,1)+1)))=D100))-1))

Year Quarter

The following array formula returns the quarter number of a month in cell B74 and the month in which quarter 1 begins in cell C74. For example, if quarter 1 begins in July, January is in the 4th quarter.

=IF(B74<C74,INDEX(ROUNDUP(ROW(INDIRECT("1:12"))/3,0),(13-C74)+B74,1),ROUNDUP((B74-C74+1)/3,0))

Easter

The following formula returns date of Easter for the year in cell B79. This is valid for years 1901 through 2099.

=FLOOR("5/"&DAY(MINUTE(B79/38)/2+56)&"/"&B79,7)-34

download You can download the file with all the example formulas and code on this page.
ShortFadeBar
LastUpdate This page last updated: 14-February-2011.