Date Functions
This page describes a number of Excel formulas for working with dates.
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.
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
 |
You can download the file with all the example formulas and code on this page. |
 |
This page last updated: 14-February-2011. |