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