×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Microsoft: Office FAQ

 Forum Search FAQs Links MVPs

Date/Time Functions

 What are some of Excel's date functions? by Bowers74 faq68-4037 Posted: 16 Aug 03 (Edited 4 Mar 06) Some of these formulas require that you have the Analysis ToolPak Add-in installed. To install this Add-in go to Tools->Add-Ins... and select the Analysis ToolPak from the list.Let's start with the simple formulas. Please note that there are various ways to get the same results below..Return the current date (w/ or w/o current time): w/ time =NOW()w/o time =TODAY()NOTE:  These formulas are "Volatile".  This means that they recalculate EVERY time the workbook recalculates.Excel stores it's dates as sequential serial numbers so they can be used in calculations. (i.e. January 1st, 1900 = 1, and January 1st, 2004 = 37987 because it's 37,986 days after January 1st, 1900.You can test this by entering this formula: =DATEVALUE(TODAY())Return the day of the month for a specific date:=DAY(TODAY())Return the day of the week for a specific date:=WEEKDAY(TODAY()) For this to work you have to set the cell format to Custom format "dddd"=TEXT(WEEKDAY(TODAY()),"dddd") ' Returns text (i.e. no calculation allowed)The WEEKDAY() formula's syntax is as follows:The first value is the date. For the second value, it depends on how the calendar is in your country.1.  1 (or omitted) = (Sunday = 1 through Saturday = 7) 2.  2 = (Monday = 1 through Sunday = 7) 3.  3 = (Monday = 0 through Sunday = 6)Return the month of the year for a specific date:=MONTH(TODAY())To have the month returned as text, either set the cell's format to Custom format "mmmm" or change the formula to:=TEXT(TODAY(),"MMMM") ' Returns text (i.e. no calculation allowed)Return the year for a specific date:=YEAR(TODAY())Return the date for a specific year, month and day:=DATE(2003,8,16) ' Returns 8/16/2003You can also use this formula in conjuction with the formulas mentioned above:=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))Adding days to a date:=TODAY()+7 ' Equals one week from todaySubtracting days from a date:=TODAY()-7 ' Equals one week ago todayReturn the last day of the current month:=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1or=EOMONTH(TODAY(),0)Return the last WORK day of the current month:=IF(WEEKDAY(EOMONTH(TODAY(),0))=6,EOMONTH(TODAY(),0)-1,IF(WEEKDAY(EOMONTH(TODAY(),0))=1,EOMONTH(TODAY(),0)-2,EOMONTH(TODAY(),0)))or=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),-1,Holidays)Return the first WORK day of the following month:=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1,1,Holidays)Inquire if the current year is a leap year:=IF(MONTH(DATE(YEAR(TODAY()),2,29))=2,TRUE,FALSE)or=IF(DAY(EOMONTH(DATE(YEAR(TODAY()),2,1),0))=29,TRUE,FALSE)Return the number of workdays in the current month:=NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1),EOMONTH(TODAY(),0),Holidays)Return the final workday in a period:=WORKDAYS(A1,B1,Holidays)A1 is the start date and B1 is the number of days in the period.A few of the previous formulas use a Named Range in Excel that contains the dates for holidays, create one using the holidays from your region and the formulas will work.DATEDIF() Syntax & ExamplesAnother way to calculate dates in Excel, is a function that has absolutely no documentation in the Excel Help File (except Excel 2000).  This function is the DATEDIF() function.  It originates from Lotus 1-2-3.The DATEDIF() function uses the following syntax:=DATEDIF(start_date,end_date,"unit_code")The start date has to be less than the end date, or else the function will return an error.The following are the Unit Codes for the DATEDIF() function:1.  "y"  -  The years in a period2.  "m"  -  The months in a period3.  "d"  -  The days in a period4.  "md" -  The difference between the days in a period, w/o the months and years5.  "ym" -  The difference between the months in a period, w/o the days and years6.  "yd" -  The difference between the days in a period, w/o the yearsTo calculate the number of years between two dates=DATEDIF(A1,TODAY(),"y") & " Years"To calculate the number of years and months between two dates=DATEDIF(A1,TODAY(),"y") & " Years, " & DATEDIF(A1,TODAY(),"ym") & " Months"To calculate the number of years, months and days between two dates=DATEDIF(A1,TODAY(),"y") & " Years, " & DATEDIF(A1,TODAY(),"ym") & " Months, "  & DATEDIF(A1,TODAY(),"md") & " Days"Using the TEXT() function to calculate datesYou can also use the TEXT() to get the number of days or weeks between two dates (based on xlbo's idea in thread68-652238):To get the number of days=VALUE(TEXT((NOW()-$A$1)/24,"[h]"))To get the number of weeks=VALUE(TEXT((NOW()-$A$1)/168,"[h]"))This can also be taken further to get the exact number of days (w/ decimal) by using "[h].m" format!To get the exact number of days (w/ hours as a decimal)=VALUE(TEXT((NOW()-$A$1)/24,"[h].m"))To get the exact number of weeks (w/ days as a decimal)=VALUE(TEXT((NOW()-$A$1)/168,"[h].m"))NOTE:  If your decimal marker is a comma "," then change the format to "[h],m".I hope this helps put some people on the right track.Peace! Mike. Back to Microsoft: Office FAQ Index Back to Microsoft: Office Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!