nbsp;   Worksheet Functions For Dates And Times 

This page describes a number of worksheet functions for working with dates and times in Excel. 
        

 

Working Days And Hours Between Two Dates And Times

Suppose you have a schedule start date and time and an end date and time.  Simply subtracting the end-time from the start-time will return the total number of days and hours between the times.  However, suppose you want to know how many work days and hours the project will actually take.  For example, suppose a project starts on Monday, 25-October-99 at 13:00 and ends on Thursday, 28-Oct-99 at 15:00, and that your normal working hours are 9:00 to 17:00.  In this case, there are 3 days and 2 hours between the start and end times (2 full days, Tuesday and Wednesday, plus 4 hours on Monday and 6 hours on Thursday, for 2 days and 10 hours, or, since there are 8 hours in a workday, 3 days and 2 hours).

The following formula will return the number of working days and hours between two dates and times.  It returns a string like "3 days 2 hours".

=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),
HolidayList,0)))),"0 days 0 hours", IF(INT(StartDT)=INT(EndDT),
"0 days " & ROUND(24*(EndDT-StartDT),2)&"hours",
MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-(StartDT-INT(StartDT)))+
(DayEnd-DayStart))/(24*(DayEnd-DayStart)))&
" days "&MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))&" hours "))

Where the following names are used: 

 

 Name  Description  Example
 StartDT  The starting date and time  25-Oct-99 13:00
 EndDT  The ending date and time  28-Oct-99 15:00
 DayStart  The time of day that the normal work day 
 begins.
 9:00
 DayEnd  The time of day that the normal work day ends.  17:00
 HolidayList  A range containing a list of dates to excluded, e.g., holidays or vacation days.  H1:H10

To return the total number of working hours, use the following formula: 

=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0))))
,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))

This will return the number of working hours as a numeric value, which can be used in other calculations.  For example, if there are 8 hours in a working day, and the duration is 3 days, 6 hours (from the first formula), the formula above will return 30 (3*8 + 6). 

Since these formulas use the NETWORKDAYS worksheet function, you must the Analysis Tool Pack installed in order to use the formula.

You can download an example workbook with these formulas here.

NOTE:  These formulas will not work properly if the working day starts at a later time of day than it ends.  For example, if your working day crosses midnight, the formulas will not work properly.

 

 

Computing Age

Computing someone's age from their birthday is simple, using the =DATEDIF function.  Suppose that a person's birthday is in A1. The following will return their age in Years, Month, and Days:

=DATEDIF(A1,NOW(),"y")&" Y, "& DATEDIF(A1,NOW(),"ym")&
" M, " & DATEDIF(A1,NOW(),"md") & " D"


Creating A Series Of Workdays

If you want to create a series of dates in a column, consisting of only weekdays (Monday through Fridays), enter your starting date in a cell (A4, in the example), and then enter the following formula in the cell below that cell.

=IF(WEEKDAY(A4)=7,A4+2,IF(WEEKDAY(A4)=6,A4+3,A4+1))

Then use Fill Down to fill out your entire series of dates.

 

 

 

 

Days Of The Week And Month

Next Day Of Week After A Date

To return the date of the next day of week following a given date, use the formula

=A1+(WEEKDAY(A1)>=C1)*7-WEEKDAY(A1)+C1

Where A1 is a date and C1 is the day-of-week number (1 = Sunday, 2 = Monday, ..., 7 = Saturday).  For example, if A1 contains the date 15-Jan-2002 (a Tuesday) and C1 contains the number 6 (indicating Friday), the formula will return 18-Jan-2002, a Friday. 

Note that if the day of week in C1 is same day of week as the date in A1, the formula returns a date 1 week later than A1.  For example, if A1 is 15-Jan-2002 (Tuesday) and C1 contains 3 (indicating Tuesday), the formula returns the date 22-Jan-2002, a Tuesday. 

If you want the formula to return the same date, use the formula

=A1+(WEEKDAY(A1)>C1)*7-WEEKDAY(A1)+C1

 

Days In Month

To return the number of days in a month, use the following formula.  Suppose cell A1 contains the date 15-Jan-1998.

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

This will return 31, the number of days in January, 1998.


First Day Of Month

You can use the following formula to get the date of the first day of the month.   Suppose the
date in cell A1 is 15-Jan-1998.

=DATE(YEAR(A1),MONTH(A1),1)

This will return 1-Jan-1998.
 

Last Day Of Month

You can use the following formula to get the date of the last day of the month.   Suppose the
date in cell A1 is 15-Jan-1998.

=DATE(YEAR(A1),MONTH(A1)+1,0)

This will return 31-Jan-1998.
 

First Weekday Day Of Month

You can use the following formula to get the date of the last weekday (Monday thru Friday) of the month.   Suppose the date in cell A1 is 1-June-2002.

=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),1,0,0,0,0,0,2)

This will return 3-June-2002.

Last Weekday Day Of Month

You can use the following formula to get the date of the last weekday (Monday thru Friday) of the month.   Suppose the date in cell A1 is 6-Nov-2002.

=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5))

This will return 29-Nov-2002.  Another formula, given a month and year, is the following array formula:

=MAX(ROW(INDIRECT(DATE(year,month,1)&":"&DATE(year,month+1,0)))*(WEEKDAY(ROW(INDIRECT(DATE(year,month,1)&":"&DATE(year,month+1,0))),2)<6))

Last Day Of Previous Month

You can use the following formula to get the date of the last day of the previous month.  Suppose the date in cell A1 is 15-Jan-1998.

=DATE(YEAR(A1),MONTH(A1),0)

This will return 31-December-1997.


Nth Day Of Week For A Month And Year

This formula will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998.   Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.

=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+
(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr, Mon, Nth,  and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.


Nth Day Of Week For A Year

This formula will return will return the date of Nth day-of-week for a given  year. For example, it will return 11-April-2001 for the 15th Wednesday of the year 2001.   Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.

=DATE(Yr,1,1+(Nth-(Dow>=WEEKDAY(DATE(Yr,1,1))))*7)+
Dow-WEEKDAY(DATE(Yr,1,1))

Where Yr,Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.


Number Of Mondays In Period

If you need to return the number of Monday's (or any other day) that occur within an interval between two dates, use the following Array Formula:

=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

This formula assumes the following:
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)


Week Differences

You cannot use the =DATEDIF function of return the difference of of two dates in weeks, so you can use the following function return the number of weeks and days between two dates:

=TRUNC((B1-A1)/7)&" Weeks "&MOD(B1-A1,7)&" Days"


What Day Is It?

If you need to know the day-of-week for a specific date, the =WEEKDAY function gives you a number between 1 and 7 indicating the day, (1=Sunday, 2=Monday, ... , 7=Saturday).   To convert this number to the actual name of the day ("Sunday", for example), use the =TEXT function:

=TEXT(A1, "ddd")
returns the three character abbreviation for the day, e.g., "Mon".
=TEXT(A1,"dddd") returns the full name of the day, e.g., "Monday".

To determine if a date is a work day (Monday - Friday), create a name called WorkDays which refers to
={2;3;4;5;6}

Then following function will return TRUE or FALSE, indicating whether the date in A1 is a work day:
=IF(ISERROR(VLOOKUP(WEEKDAY(A1),WorkDays,1,0)),FALSE,TRUE)


What Quarter Is It?

To return the quarter of the year, use the formula 

=ROUNDUP(MONTH(A1)/3,0) .


What Week Is It?

If you need to know what week of the year at date falls in, use the following formula:

=TRUNC(((A1-DATE(YEAR(A1),1,1))/7))+1+
IF(WEEKDAY(DATE(YEAR(A1),1,1))>WEEKDAY(A1),1,0)

This will return a number between 1 and 53, indicating the week of the year for the date in A1.

 

Last Weekday Of Month

 

        
Latitude And Longitude

You can use the Excel's time format to work with latitude and longitude data, expressed in 
Degrees:Minutes:Seconds
format.   See the Latitude And Longitude page for more details.

 

 

 

Other Date And Time Related Procedures are described on the following pages.

        

Adding Months And Years

The DATEDIF Function

Date Intervals

Dates And Times

Date And Time Entry

Date And Time Arithmetic

Distributing Dates Over Intervals

Holidays

Julian Dates

Latitude And Longitude 

Overtime Hours And Timesheets


Time Zones In VBA

VBA Procedures For Dates And Times

Week Numbers

Worksheet Functions For Dates And Times

 

 

See the Dates And Times Topic Index For Information