Excel Date and Time Functions & Formulas
Excel Dates Equate to Serial Numbers:
Excel stores all dates as integers and all times as decimal fractions. With this system, Excel can add, subtract, or compare dates and times just like any other numbers, and all dates are manipulated by using this system. In this system, the serial number 1 represents 1/1/1900 12:00:00 AM, the first supported day from when the Excel calendar starts. Bug in Excel: Excel erroneously treats 1900 as a leap year, which has presumably been done knowingly by Microsoft to provide compatibility with Lotus 1-2-3, and so actually the bug would have been in Lotus 123 (Excel’s predecessor).
In Excel, Dates equate to a “serial number” (which is a numeric value) that is the count of number of days elapsed from a certain referenced date. The integer part (values to the left of decimal) is the number of days elapsed since January 1, 1900. For example, January 1, 1900 is stored as 1; January 2, 1900 is stored as 2; March 15, 2001 is stored as 36,965. The fractional part (values to the right of decimal) holds time information, and represents the time as a fraction of a whole day. For example, 12.00AM (midnight) is stored as 0; 6:00AM is stored as 0.25; 12.00PM (noon) is stored as 0.5; 6:00PM is stored as 0.75; 6:00:30PM is stored as 0.750347222. To check the “serial number” of a date and time simply format the cell as “General”. The Date and Time of “10/3/1954 6:00:00 AM” has a serial number of 20000.25.
————————————————————————————————————
Calculate Age from Birth Date:
=DATEDIF(A1,NOW(),”Y”) &” Years” [Formula]
Formula returns Age in Years, where Date of Birth is in cell A1.
=DATEDIF(A1,NOW(),”Y”) & ” Years & “& DATEDIF(A1,NOW(), “YM”) & ” Months” [Formula]
Formula returns Age in Years & Months, where Date of Birth is in cell A1.
=DATEDIF(A1,NOW(),”Y”) &” Years, “& DATEDIF(A1,NOW(), “YM”) & ” Months & ” & DATEDIF(A1,NOW(), “MD”) & ” Days” [Formula]
Formula returns Age in Years, Months & Days, where Date of Birth is in cell A1.
DATEDIF function: To calculate the difference between two dates, use the DATEDIF function (undocumented in most excel versions) in excel. Syntax: DateDif(Start_Date, End_Date, Interval). Interval is the time unit used to calculate the difference between Start Date and End Date viz. “Y” is used for completed Years, “M” for completed Months, “D” for completed Days, “YM” for difference in Months within a year (excludes years), “YD” for difference in Days within the year (excludes years) and “MD” for difference in Days within the same month and year.
Difference between two Dates:
Calculate number of days between two dates: =A2-A1.
Calculate Weekdays between two dates: =NETWORKDAYS(A1, A2).
Calculate Months occurring between two dates within the year: =MONTH(A2)-MONTH(A1).
Calculate Months occurring between two dates in different years: =(YEAR(A3)-YEAR(A2))*12+ MONTH(A3)- MONTH(A2).
Calculate number of years between two dates: =YEAR(A3)- YEAR(A2).
NETWORKDAYS Function: Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Syntax: NETWORKDAYS(start_date, end_date, holidays). Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays.
MONTH Function: Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December). Syntax: MONTH(serial_number). Serial_number is the date of the month you are trying to find.
YEAR Function: Returns the year corresponding to a date. Syntax: YEAR(serial_number). Serial_number is the date of the year you want to find.
Calculate Days in a Month:
=DAY(DATE(YEAR(A1), MONTH(A1)+1, 0)) [Formula]
If cell A1 contains the date 15-May-08, then the formula will return 31 (ie. number of days in the month of May).
=DATE(YEAR(A1), MONTH(A1)+1, 0) part of the formula evaluates to 31-May-08. =DATE(YEAR(A1), MONTH(A1), 0) returns 30-Apr-08. To get the relevant month (ie. May), 1 is added to the MONTH.
Below functions explain this formula:
DATE Function: Returns the sequential serial number that represents a particular date. If the cell format was General before the function was entered, the result is formatted as a date. Syntax: DATE(year,month,day). Year The year argument can be one to four digits. Month is a positive or negative integer representing the month of the year from 1 to 12 (January to December). If month is greater than 12, month adds that number of months to the first month in the year specified. For example, DATE(2008,14,2) returns the serial number representing February 2, 2009. If month is less than 1, month subtracts that number of months plus 1 from the first month in the year specified. For example, DATE(2008,-3,2) returns the serial number representing September 2, 2007. Day is a positive or negative integer representing the day of the month from 1 to 31. If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. For example, DATE(2008,1,35) returns the serial number representing February 4, 2008. If day is less than 1, day subtracts that number of days plus one from the first day in the month. For example, DATE(2008,1,-15) returns the serial number representing December 16, 2007.
DAY Function: Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31. Syntax: DAY(serial_number). Serial_number is the date of the day you are trying to find.
Alternate Formula: =DAY(DATE(YEAR(A1), MONTH(A1)+1, 1)-1).
If cell A1 contains the date 15-May-08, then the formula will return 31 (ie. number of days in the month of May).
=DATE(YEAR(A1), MONTH(A1)+1, 1) evaluates to 1-Jun-08.
=DATE(YEAR(A1), MONTH(A1)+1, 1)-1 evaluates to 31-May-08.
VBA Code to Calculate Days in a Month:
Sub DaysInMonth()
‘If cell A1 contains the date 15-May-08, then this VBA code will return 31 (ie. number of days in the month of May).
Dim y As Integer
Dim m As Integer
Dim Days As Integer
m = Month(Range(“A1”))
y = Year(Range(“A1”))
Days = DateSerial(y, m + 1, 1) – DateSerial(y, m, 1)
‘Syntax for DateSerial VBA function is: DateSerial(year, month, day). It returns a date, when the year, month, and day value are specified.
MsgBox Days
End Sub
Get First and Last Day of a Month:
Get First Day of the Month: =DATE(YEAR(A1), MONTH(A1), 1). If cell A1 contains the date 15-May-08, then the formula will return 1-May-08.
Get First Day of the Month: =EOMONTH(A1,-1)+1. If cell A1 contains the date 15-May-08, then the formula will return 1-May-08.
Get Last Day of the Month: =DATE(YEAR(A1), MONTH(A1)+1,0). If cell A1 contains the date 15-May-08, then the formula will return 31-May-08.
Get Last Day of the Month: =DATE(YEAR(A1), MONTH(A1)+1,1)-1. If cell A1 contains the date 15-May-08, then the formula will return 31-May-08.
Get Last Day of the Month: =EOMONTH(A1, 0). If cell A1 contains the date 15-May-08, then the formula will return 31-May-08.
Get Last Day of the Previous Month: =DATE(YEAR(A1), MONTH(A1), 0). If cell A1 contains the date 15-May-08, then the formula will return 30-Apr-08.
Get Last Day of the Previous Month: =EDATE(DATE( YEAR(A1), MONTH(A1)+1, 0),-1). If cell A1 contains the date 15-May-08, then the formula will return 30-Apr-08.
EDATE Function returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Syntax: EDATE(start_date,months). Months is the number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date.
Get Last Day of the Previous Month: =EDATE(DATE(YEAR(A1), MONTH(A1)+1, 1)-1,-1). If cell A1 contains the date 15-May-08, then the formula will return 30-Apr-08.
Get Last Day of the Previous Month: =EOMONTH(A1, -1). If cell A1 contains the date 15-May-08, then the formula will return 30-Apr-08.
Get First and Last Day of Current/ Following/ Previous Month:
Get First Day of the Following Month (ie. after the Current Month): =EOMONTH(TODAY(), 0)+1.
Get First Day of the Current Month: =EDATE(EOMONTH(TODAY(), 0)+1,-1).
Get Last Day of the Current Month: =EOMONTH(TODAY(), 0).
Get Last Day of the Previous Month (ie. before the Current Month): =EDATE(EOMONTH(TODAY(), 0),-1).
EOMONTH Function: Returns the date (serial number) for the last day of the month that is the indicated number of months before or after start_date. Syntax: EOMONTH(start_date, months). A positive value for months yields a future date; a negative value yields a past date.
Find First Working or Business Day of a Month:
=WORKDAY(A1-DAY(A1), 1) [Formula]
If cell A1 contains the date 15-May-08, then the formula will return 1-May-08 (which is a Thursday). Formula =WORKDAY(A1-DAY(A1),2) will return 2-May-08, which is a Friday. Formula =WORKDAY(A1-DAY(A1), 3) will return 5-May-08, which is a Monday.
Formula Part =A1-DAY(A1) returns the date 30-Apr-08 (ie. last day of previous month), and using the WORKDAY Function with “days” value as 1, returns the first Working Day after 30-Apr-08.
WORKDAY Function: Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Syntax: WORKDAY(start_date, days, holidays). Start_date is a date that represents the start date. Days is the number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date. Holidays is an optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays.
Note: WORKDAY is part of the Analysis Toolpak, so that add-in needs to be installed in your workbook.
=WORKDAY(EOMONTH(A1,-1), 1) [Formula]
If cell A1 contains the date 15-May-08, then the formula will return 1-May-08 (which is a Thursday).
Formula Part =EOMONTH(A1,-1) returns the date 30-Apr-08 (ie. last day of previous month), and using the WORKDAY Function with “days” value as 1, returns the first Working Day after 30-Apr-08.
=IF(WEEKDAY(DATE(YEAR(A1), MONTH(A1),1))=1, DATE(YEAR(A1), MONTH(A1),1)+1, IF(WEEKDAY( DATE(YEAR(A1), MONTH(A1),1))=7, DATE( YEAR(A1), MONTH(A1),1)+2, DATE(YEAR(A1), MONTH(A1),1))) [Formula]
If cell A1 contains the date 15-May-08, then the formula will return 1-May-08 (which is a Thursday).
Formula Part =DATE(YEAR(A1), MONTH(A1), 1) gives first day of month. Combined with WEEKDAY function, it gives its day number. If WEEKDAY is 1 (ie. Sunday), it adds one day, if WEEKDAY is 7 (ie. Saturday) then it adds 2 days, else returns the first day itself.
Formula appears more complex than it actually might be.
=DATE(YEAR(A1), MONTH(A1),1)+ CHOOSE(WEEKDAY(DATE( YEAR(A1), MONTH(A1),1)), 1,0,0,0, 0,0,2) [Formula]
If cell A1 contains the date 15-May-08, then the formula will return 1-May-08 (which is a Thursday).
Formula Part =DATE( YEAR(A1), MONTH(A1),1) gives first day of month. Combined with WEEKDAY function, it gives its day number. If day number is 1 (ie. Sunday) or 7 (Saturday), it adds (by CHOOSING) 1 or 2 to the first day of month, else no addition is made.
Logic of this formula is similar to the above/preceding formula, but this is more compact.
Find Last Working or Business Day of a Month:
=WORKDAY(DATE(YEAR(A1), MONTH(A1)+1, 1), -1) [Formula]
If cell A1 contains the date 15-May-08, then the formula will return 30-May-08, which is the last working day of May ie. Friday.
Formula Part =DATE(YEAR(A1),MONTH(A1)+1,1) returns the first day of the next month. Combined with WORKDAY function and the value -1, the negative value yields a past date (ie. working day before the date).
=WORKDAY(EOMONTH(A1,0)-7, 5) [Formula]
If cell A1 contains the date 15-May-08, then the formula will return 30-May-08, which is the last working day of May ie. Friday.
Formula Part =EOMONTH(A1, 0) returns the last day of the month (31-May-08). Deducting 7 from the last day will return the day after which exactly one week of the month is left (24-May-08). The fifth work day from this date, is the last working day of the month as returnd by the formula.
=DATE(YEAR(A1), MONTH(A1)+1, 0)-(MAX(0, WEEKDAY(DATE(YEAR(A1), MONTH(A1)+1,0), 2)-5)) [Formula]
If cell A1 contains the date 15-May-08, then the formula will return 30-May-08, which is the last working day of May ie. Friday.
Formula Part =DATE(YEAR(A1),MONTH(A1)+1,0) returns the last day of the month (31-May-08). If it is a Saturday or Sunday (ie. WEEKDAY 6 or 7), then 1 day or 2 days (ie. 6 minu 5 or 7 minus 5) are deducted from the last day.
=IF(WEEKDAY(EOMONTH(A1, 0))=1, EOMONTH(A1,0)-2, IF(WEEKDAY( EOMONTH(A1,0))=7, EOMONTH(A1,0)-1, EOMONTH(A1, 0))) [Formula]
If cell A1 contains the date 15-May-08, then the formula will return 30-May-08, which is the last working day of May ie. Friday.
In the formula, WEEKDAY(EOMONTH(A1, 0))=1 indicates a Sunday, WEEKDAY(EOMONTH(A1, 0))=7 indicates a Saturday, hence in these 2 cases change in the last day is required.
Find Last Working Day of Previous Month:
=WORKDAY(DATE(YEAR(A1), MONTH(A1), 1),-1) [Formula]
If cell A1 contains the date 3-Aug-10, then the formula will return 30-Jul-10, which is the last working day of July ie. Friday.
Formula Part =DATE(YEAR(A1), MONTH(A1),1) returns the first day of the month. Combined with WORKDAY function and the value -1, the negative value yields a past date (ie. working day before the date).
=WORKDAY(EOMONTH(A1,-1)-7,5) [Formula]
If cell A1 contains the date 3-Aug-10, then the formula will return 30-Jul-10, which is the last working day of July ie. Friday.
Formula Part EOMONTH(A1,-1) returns the last day of the previous month (31-Jul-10). Deducting 7 from the last day will return the day after which exactly one week of the month is left (24-Jul-10). The fifth work day from this date, is the last working day of the month as returnd by the formula.
=DATE(YEAR(A1), MONTH(A1), 0)-(MAX(0, WEEKDAY(DATE(YEAR(A1), MONTH(A1),0), 2)-5)) [Formula]
If cell A1 contains the date 3-Aug-10, then the formula will return 30-Jul-10, which is the last working day of July ie. Friday.
Formula Part =DATE(YEAR(A1), MONTH(A1), 0) returns the last day of the previous month (31-Jul-10). If it is a Saturday or Sunday (ie. WEEKDAY 6 or 7), then 1 day or 2 days (ie. 6 minu 5 or 7 minus 5) are deducted from the last day.
Find Day of the Week (convert date to text):
=TEXT(A1, “dddd”) [Formula]
Calculates the day of the week for the date and returns the full name of the day of the week, viz. Thursday.
=TEXT(A1, “ddd”) [Formula]
Calculates the day of the week for the date and returns the abbreviated name of the day of the week, viz. Thu.
Calculate Week Number of a Date:
=WEEKNUM(A1) [Formula]
Returns the week number (in a year) of a date using the WEEKNUM formula.
WEEKNUM Function: Returns a number that indicates where the week falls numerically within a year. Syntax: WEEKNUM(serial_num, return_type). Serial_num is a date within the week. Return_type is a number that determines on which day the week begins. The default is 1. Return-type 1 indicates week begins on Sunday and weekdays are numbered 1 through 7. Return-type 2 indicates week begins on Monday and weekdays are numbered 1 through 7.
Note: WEEKNUM is part of the Analysis Toolpak, so that add-in needs to be installed in your workbook.
Calculate Quarter Number of a Date:
=ROUNDDOWN((MONTH(A1)-1)/3, 0)+1 [Formula]
=INT((MONTH(A1)-1)/3)+1 [Formula]
=ROUNDUP(MONTH(A1)/3, 0) [Formula]
The 3 formulas calculate the quarter number of a date for a calendar year.
Nth Xday after a date:
=A1+7*n-WEEKDAY(A1+7-x) [Generic Formula]
=A1+7*1-WEEKDAY(A1+7-7) [Formula for First Saturday after a Date]
If cell A1 contains the date 15-May-08, then the formula “=A1+7*1- WEEKDAY(A1+7-7)” will return 17-May-08, which is the first (Nth) Saturday (Xday) on or after the date in cell A1.
Formula Part =A1+7*1 returns the date 22-May-08 (by adding 7 days to the date 15-May-08). Formula Part =WEEKDAY(A1+7-7) returns 5. Decreasing 5 days from 22-May-08 returns 17-May-08.
If the date in cell A1 is 1-Nov-08 which is a Saturday, then the formula will also return the same date ie. 1-Nov-08 itself.
Note: Xday for this formula is 1 for Sunday till 7 for Saturday.
Nth Xday of the Month (Date of the Nth Specific Day of the Month):
=DATE(YEAR(A1), MONTH(A1),1)+7*n- WEEKDAY(DATE(YEAR(A1), MONTH(A1),1)+7-x) [Generic Formula]
=DATE(YEAR(A1), MONTH(A1),1)+7*1- WEEKDAY(DATE(YEAR(A1), MONTH(A1), 1)+7-7) [Formula for First Saturday of a Month]
If cell A1 contains the date 15-May-08, then the formula “=DATE(YEAR(A1), MONTH(A1),1)+7*1-WEEKDAY(DATE(YEAR(A1), MONTH(A1),1)+ 7-7)” will return 3-May-08, which is the first (Nth) Saturday (Xday) of the month (May-08).
Formula logic is same as above, only the date in cell A1 has been converted to the first day of the month with formula =DATE(YEAR(A1),MONTH(A1),1).
Note: Xday for this formula is 1 for Sunday till 7 for Saturday.
=DATE(YEAR(A1), MONTH(A1), 1+((n-(x>=WEEKDAY(DATE(YEAR(A1), MONTH(A1),1), 2)))*7+ (x-WEEKDAY( DATE( YEAR(A1), MONTH(A1), 1), 2)))) [Generic Formula]
=DATE(YEAR(A1), MONTH(A1), 1+((1-(6>=WEEKDAY( DATE(YEAR(A1), MONTH(A1),1), 2)))*7+ (6-WEEKDAY(DATE( YEAR(A1), MONTH(A1), 1), 2)))) [Formula for First Saturday of a Month]
If cell A1 contains the date 15-May-08, then the formula will return 3-May-08, which is the first (Nth) Saturday (Xday) of the month (May-08).
Note: Xday for this formula is 1 for Monday till 7 for Sunday.
Number of Xdays between 2 Dates (Number of Mondays between 2 Dates, Number of Wednesdays, Thursdays & Fridays between 2 Dates, ….):
{=SUM(IF(WEEKDAY(FirstDate-1+ROW(INDIRECT(“1:”&(SecondDate-FirstDate)+1)))= {x1,x2,x3,x4, x5,x6,x7}, 1,0))} [Generic Formula]
Xdays can be singular or multiple and anywhere between 1 to 7 (x1,x2, ….). Formula can return number of Mondays between 2 dates, it can return number of Mondays, Tuesdays, Wednesdays & Thursdays between 2 dates, and so on.
Enter as an array formula: type the formula in the cell and then press CTRL+SHIFT+ENTER instead of just ENTER. Excel will automatically display the formula enclosed in braces { }.
{=SUM(IF(WEEKDAY(A1-1+ ROW(INDIRECT(“1:”& (B1-A1)+1)))={4,5, 6}, 1,0))} [Formula for number of Wednesdays, Thursdays & Fridays between 2 dates]
If cell A1 contains the date 15-May-08 and B1 contains the date 31-May-08, then the formula will return 8, which are the number of Wednesdays, Thursdays & Fridays between the 2 dates.
Note: Xday for this formula is 1 for Sunday till 7 for Saturday.
=SUMPRODUCT(–(WEEKDAY( ROW(INDIRECT( A1&”:”&B1)))={4, 5,6})) [Formula for number of Wednesdays, Thursdays & Fridays between 2 dates]
If cell A1 contains the date 15-May-08 and B1 contains the date 31-May-08, then the formula will return 8, which are the number of Wednesdays, Thursdays & Fridays between the 2 dates.
Xdays can be singular or multiple and anywhere between 1 to 7 (x1,x2, ….). Formula can return number of Mondays between 2 dates, it can return number of Mondays, Tuesdays, Wednesdays & Thursdays between 2 dates, etc.
Note: Xday for this formula is 1 for Sunday till 7 for Saturday.