Beyond the Help files . . .

 

 

Access    Excel  FrontPage  Outlook  PowerPoint   Word     Miscellaneous    Tutorials    Windows    Free Downloads


 


 

Date Formulas and Functions in Microsoft Excel

Excel stores dates in sequential serial numbers beginning with January 1st, 1900 as 1.  January 1st, 2006 is 38718 because it's 38,718 days after January 1st, 1900.  Excel on MAC works the same, except the dates start on January 1st, 1904. Don't ask me why.

Note: There are several different ways to do everything in Microsoft Office applications. Date formulas and functions are no different, so this list isn't comprehensive, but should get you by until you become an expert.

Note: Some of these formulas and functions require use of the Analysis Toolpak Addin. Just go to ToolsAddins, and put a check next to Analysis Toolpak if you get a #NAME? error.

Note: Some of these formulas and functions use a named range called "Holidays". To create it, make a list of dates in a column. Assign the named range to the cells.

Tip: Volatile formulas are formulas that recalculate every time the workbook calculates. The =NOW formula (among others) is a Volatile formula. If you open a workbook that contains a volatile formula, you make no changes, and close it, don't be surprised if you're asked to save the workbook. That's because the value that the recalculation created did, in fact, change in your workbook.

Date Formulas and Functions

=NOW()

=TODAY()

=DAY(TODAY())

=WEEKDAY(TODAY())

=TEXT(WEEKDAY(TODAY()),"dddd")

=MONTH(TODAY())

=YEAR(TODAY())

=DATE(2005,6,12)

=TODAY()+7

=TODAY()-7

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1

or

=EOMONTH(TODAY(),0)

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

=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1,1,Holidays)

=NETWORKDAYS(DATE(YEAR(TODAY()),MONTH(TODAY()),1),EOMONTH(TODAY(),0),Holidays)

=WORKDAYS(A1,B1,Holidays)

DATEDIF() Syntax & Examples

The DATEDIF() function uses the following syntax:

=DATEDIF(start_date,end_date,"code")

The start date must be less than the end date, or the function returns an error.

The following are the codes for the DATEDIF() function:

Examples

=DATEDIF(A1,TODAY(),"y") & " Years"

=DATEDIF(A1,TODAY(),"y") & " Years, " & DATEDIF(A1,TODAY(),"ym") & " Months"

=DATEDIF(A1,TODAY(),"y") & " Years, " & DATEDIF(A1,TODAY(),"ym") & " Months, " & DATEDIF(A1,TODAY(),"md") & " Days"

Using the TEXT() function to calculate dates.

You can use the TEXT() to get the number of days or weeks between two dates:

Examples

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

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

 

Back to top...

 

 

All contents copyright Anne Troy 2005-2006