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 endtime from the starttime 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, 25October99 at 13:00 and ends on Thursday, 28Oct99 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), Where the following names are used:
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)))) 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 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")& 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))


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)*7WEEKDAY(A1)+C1
Where A1 is a date and C1 is the dayofweek number (1 =
Sunday, 2 = Monday, ..., 7 = Saturday). For example, if A1 contains
the date 15Jan2002 (a Tuesday) and C1 contains the number 6 (indicating
Friday), the formula will return 18Jan2002, a Friday.
To return the number of days in a month, use the following formula. Suppose cell A1 contains the date 15Jan1998. =DAY(DATE(YEAR(A1),MONTH(A1)+1,0)) This will return 31, the number of days in January, 1998.
You can use the following formula to get the date of the first day of the month.
Suppose the =DATE(YEAR(A1),MONTH(A1),1) Last Day Of Month You can use the following formula to get the date of the last day of the month.
Suppose the =DATE(YEAR(A1),MONTH(A1)+1,0) 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 1June2002. =DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),1,0,0,0,0,0,2) This will return 3June2002. 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 6Nov2002.
=DATE(YEAR(A1),MONTH(A1)+1,0)(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)5)) =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 15Jan1998. =DATE(YEAR(A1),MONTH(A1),0)
This formula will return will return the date of Nth dayofweek for a given month and year. For example, it will return 26March98 for the 4th Thursday of March, 1998. Daysofweek range from 1 to 7, with Sunday = 1 and Saturday = 7. =DATE(Yr,Mon,1+((Nth(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+ Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and DayOfWeek. This formula will return will return the date of Nth dayofweek for a given year. For example, it will return 11April2001 for the 15th Wednesday of the year 2001. Daysofweek range from 1 to 7, with Sunday = 1 and Saturday = 7. =DATE(Yr,1,1+(Nth(Dow>=WEEKDAY(DATE(Yr,1,1))))*7)+ Where Yr,Nth, and DoW are cell references or values indicating Year, Month, Nth, and DayOfWeek. 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: This formula assumes the following: 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((B1A1)/7)&" Weeks "&MOD(B1A1,7)&" Days"
If you need to know the dayofweek 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:
To determine if a date is a work day (Monday  Friday), create a name called WorkDays which refers to Then following function will return TRUE or FALSE, indicating whether the date in A1 is
a work day: To return the quarter of the year, use the formula =ROUNDUP(MONTH(A1)/3,0) .
If you need to know what week of the year at date falls in, use the following formula: =TRUNC(((A1DATE(YEAR(A1),1,1))/7))+1+ 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




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

Holidays 

