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 ToolsŕAddins, 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
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
Date Formulas and Functions
Today's date and time:
Today's date only:
Day of the month for a specific date:
Day of the week for a specific date (set the cell format to custom format
Month of the year for a specific date:
Year for a specific date:
Date for a specific year, month and day (Example returns 6/12/2005):
MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on
this web site are provided "as is" and we do not guarantee that they can be used
in all situations.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered
trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.