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".

Where the following names are used: 

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

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.

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:

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

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