What are some of Excel's date functions?

Some of these formulas require that you have the Analysis ToolPak Add-in installed.
To install this Add-in go to [color green]Tools->Add-Ins...[/color] and select the Analysis ToolPak from the list.

Let's start with the simple formulas.

[color red]Please note that there are various ways to get the same results below..[/color]


Return the current date (w/ or w/o current time):

w/ time [color blue]=NOW()[/color]
w/o time [color blue]=TODAY()[/color]

[color red]NOTE: These formulas are "Volatile". This means that they recalculate EVERY time the workbook recalculates.[/color]

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: [color blue]=DATEVALUE(TODAY())[/color]



Return the day of the month for a specific date:

[color blue]=DAY(TODAY())[/color]

Return the day of the week for a specific date:

[color blue]=WEEKDAY(TODAY())[/color] [color green]For this to work you have to set the cell format to Custom format "dddd"[/color]

[color blue]=TEXT(WEEKDAY(TODAY()),"dddd")[/color] [color green] ' Returns text (i.e. no calculation allowed)[/color]

[color red]The [/color][color blue] WEEKDAY()[/color][color red] formula's syntax is as follows:[/color]

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:

[color blue]=MONTH(TODAY())[/color]

To have the month returned as text, either set the cell's format to Custom format "mmmm" or change the formula to:

[color blue]=TEXT(TODAY(),"MMMM")[/color] [color green] ' Returns text (i.e. no calculation allowed)[/color]


Return the year for a specific date:

[color blue]=YEAR(TODAY())[/color]


Return the date for a specific year, month and day:

[color blue]=DATE(2003,8,16)[/color] [color green]' Returns 8/16/2003[/color]

You can also use this formula in conjuction with the formulas mentioned above:

[color blue]=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))[/color]


Adding days to a date:

[color blue]=TODAY()+7[/color] [color green] ' Equals one week from today[/color]


Subtracting days from a date:

[color blue]=TODAY()-7[/color] [color green] ' Equals one week ago today[/color]


Return the last day of the current month:

[color blue]=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1[/color]

or

[color blue]=EOMONTH(TODAY(),0)[/color]


Return the last WORK day of the current month:

[color blue]=IF(WEEKDAY(EOMONTH(TODAY(),0))=6,EOMONTH(TODAY(),0)-1,IF(WEEKDAY(EOMONTH(TODAY(),0))=1,EOMONTH(TODAY(),0)-2,EOMONTH(TODAY(),0)))[/color]

or

[color blue]=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),-1,Holidays)[/color]


Return the first WORK day of the following month:

[color blue]=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1,1,Holidays)[/color]


Inquire if the current year is a leap year:

[color blue]=IF(MONTH(DATE(YEAR(TODAY()),2,29))=2,TRUE,FALSE)[/color]

or

[color blue]=IF(DAY(EOMONTH(DATE(YEAR(TODAY()),2,1),0))=29,TRUE,FALSE)[/color]



Return the number of workdays in the current month:

[color blue]=NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1),EOMONTH(TODAY(),0),Holidays)[/color]


Return the final workday in a period:

[color blue]=WORKDAYS(A1,B1,Holidays)[/color]
[color green]A1 is the start date and B1 is the number of days in the period.[/color]

[color red]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.[/color]


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 [color blue]DATEDIF()[/color] function. It originates from Lotus 1-2-3.

The [color blue] DATEDIF()[/color] function uses the following syntax:

[color blue]=DATEDIF(start_date,end_date,"unit_code")[/color]

[color red]The start date has to be less than the end date, or else the function will return an error.[/color]

The following are the Unit Codes for the [color blue]DATEDIF()[/color] 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

[color blue]=DATEDIF(A1,TODAY(),"y") & " Years"[/color]

To calculate the number of years and months between two dates

[color blue]=DATEDIF(A1,TODAY(),"y") & " Years, " & DATEDIF(A1,TODAY(),"ym") & " Months"[/color]

To calculate the number of years, months and days between two dates

[color blue]=DATEDIF(A1,TODAY(),"y") & " Years, " & DATEDIF(A1,TODAY(),"ym") & " Months, " & DATEDIF(A1,TODAY(),"md") & " Days"[/color]


Using the TEXT() function to calculate dates

You can also use the [color blue]TEXT()[/color] 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

[color blue]=VALUE(TEXT((NOW()-$A$1)/24,"[h]"))[/color]

To get the number of weeks

[color blue]=VALUE(TEXT((NOW()-$A$1)/168,"[h]"))[/color]

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)

[color blue]=VALUE(TEXT((NOW()-$A$1)/24,"[h].m"))[/color]

To get the exact number of weeks (w/ days as a decimal)

[color blue]=VALUE(TEXT((NOW()-$A$1)/168,"[h].m"))[/color]

[color red]NOTE: If your decimal marker is a comma "," then change the format to "[h],m".[/color]

I hope this helps put some people on the right track.



Peace!

Mike


.