INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Log In
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips Forums!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
- Students Click Here
*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. Students Click Here
|
Date/Time Functions
|
What are some of Excel's date functions? by Bowers74
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/2003
You 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 today
Subtracting days from a date:
=TODAY()-7 ' Equals one week ago today
Return the last day of the current month:
=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1
or
=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 & Examples
Another 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 period
2. "m" - The months in a period
3. "d" - The days in a period
4. "md" - The difference between the days in a period, w/o the months and years
5. "ym" - The difference between the months in a period, w/o the days and years
6. "yd" - The difference between the days in a period, w/o the years
To 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 dates
You 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
|
|
|
|
Talk To Other Members