Excel Chapter 15 of 24: Excel Date & Time Functions and Formulas

The three most important things that you should remember when working with dates and times are:
FORMAT, FORMAT and FORMAT.

For example:

Let's say that you have the dates 1/16/2005 in cell A1 and 1/22/2005 in cell B1
=B1-A1 in cell C1 will return:

- 6 if the format of cell C1 is either "General" or "Number"
- 1/6/1900 if the format of cell C1 is "Date"

Using the proper formats you might want to work dates even without date & Time functions:

If you have a date in cell A1 and you want the date for the next day in cell B1 (formatted "date" ) the formula will be:
=A1+1
to calculate the date of a week later the formula will be:
=A1+7

Excel offers you 20 functions in the date & time category. See them all with a short description in Excel Spreadsheets Date and Time Functions (chapter 24). Here are the functions (12) that you will use more often with tips and examples.
Functions	What it Does
DATE	Returns the serial number of a particular date
DAY	Converts a serial number to a day of the month
HOUR	Converts a serial number to an hour
MINUTE	Converts a serial number to a minute
MONTH	Converts a serial number to a month
NOW	Returns the serial number of the current date and time
SECOND	Converts a serial number to a second
TIME	Returns the serial number of a particular time
TODAY	Returns the serial number of today's date
WEEKDAY	Converts a serial number to a day of the week
YEAR	Converts a serial number to a year
DATEDIF	Calculates the interval between two dates

Tips on Excel Date and Time Functions and Formulas

If you enter the date 2/1/2005 in cell A1 and the number format of the cell is " General" you will see 38394. This is a " Serial number" and it is the way Excel works with dates and times. When you format the cell or use one of the functions below the serial number is viewed as times and dates

To enter the date of the day no need to key it in, click and hold the CTRL key and click on the semi-colon key ( ) and there is the date.

To enter the time, click and hold both the Shift and the CTRL keys and click on the colon key (:) and there is the time.

Microsoft Excel doesn't work with dates and times, it works with serial numbers This means that when you enter 12/25/2004 Excel sees 38346 and if you enter 12/26/2004 Excel sees 38347. When you enter 12:00:00 PM Excel reads 0.5 and if you write 12:00:01 PM Excel reads 0.5000116. It is when you format the cell " Format/Cells" that you can read dates and times as we humans are accustomed to see them.

By the way, I was born on 18373 at 0.25 so I am an Aries, and you?

This being said, most functions of the category Date & Time are quite easy to work with when you use the right cell format. For example, when you are adding times and expect the total to be over 24 hours you must set the format of the result cell to " Format/Cells/Time/37:30:55"

If you develop a time management application don't go through the trouble of working with clock time. Ask your people to enter either the number of hours worked by projects or the number of minutes then work with regular numbers. Much easier.

Examples of basic Excel Date and Time Formulas

DATEDIF

One note to start. If you go to the menu " Insert/Function" you won't find this function. Excel has forgotten it. Here is how it works. Let's say that you have the dates 1/16/2005 in cell A1 and 1/16/2006 in cell B1:
=DATEDIF(A1,B1,"y") will return 1
=DATEDIF(A1,B1,"m") will return 12
=DATEDIF(A1,B1,"d") will return 365

DAY, MONTH, YEAR

With a date in A1 like 12/15/2005 the formulas =DAY(A1), =MONTH(A1) and =YEAR(A1) will return 15, 12 and 2005.

SECOND, MINUTE, HOUR

With a TIME in A1 like 1:31:45PM the formulas =SECOND(A1), =MINUTE(A1) and =HOUR(A1) will return 1, 31 and 45.

WEEKDAY

If the date in A1 is 1/16/2006 and it is a Monday the formula =WEEKDAY(A1) will return 2. For most users day 1 is Sunday. Check what your system says because in some cases day 1 is Monday.

DATE, DAY, MONTH, YEAR

With the DATE function, the arguments are always in the following order (year,month,day) whatever the date format specified in your regional parameters.

With a date in cell A1
the formula to add a day is:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+1)
the formula to add a week is :
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+7)
the formula to add a month is:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
the formula to add a year is:
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))
the last day of the month preceding the date in A1 is:
=DATE(YEAR(A1),MONTH(A1),DAY(A1)-DAY(A1))
the first day of the month following the date in A1 is:
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)-DAY(A1)+1)

MONTH, DAY, NOW, AND and IF (Anniversary Alerts)

My client wanted a spreadsheet that would tell her when it is the birthday of an employee. We created a spreadsheet with the names in column A and the dates of birth in column B. In column D was this formula =NOW() that changes date each day. In column C we put this formula:
=IF(AND(MONTH(B1)-MONTH(D1)=0,DAY(B1)-DAY(D1)=0),"Anniversary","")

We fine tuned:
=IF(AND(MONTH(B1)-MONTH(D1)=0,DAY(B1)-DAY(D1)=0),"Happy Anniversary" ,IF(AND(MONTH(B1)-MONTH(D1)=0,DAY(B1)-DAY(D1)>0,DAY(B1)-DAY(D1)<7)," Anniversary coming" ,"" ))

Copy/paste the formula above in your spreadsheet. If you want to be alerted more that a week before the anniversary change the 7 for 30 in the formula. This way you will be alerted a month in advance.

Next Chapter: Financial Functions and Formulas in Excel

Excel Table of Contents
or use the site map link below

Discover Even More in 50 Excel spreadsheets

613-749-4695
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

Excel Date & Time Functions and Formulas