Using MS Excel Date and Time Functions
How To Use and Convert Dates or Times in Microsoft XL
Jul 24, 2009
When a date or date and time are entered into an Excel cell, the value is stored as a number. The number stored is the number of days, with decimals, since the 1st Jan, 1900. Excel provides around 15 different formats (see Figure 1) with which to display the date and time. It also supplies about 20 functions (see figure 2) that may be used to provide information about dates and times.
Using Excel To Store Date and Time – Examples
Excel cannot handle dates before 1st Jan, 1900. Other dates are stored as numbers. The following are examples:
Number ----> Date
1 --------------> 1 Jan, 1900 (This is the base date in Excel)
2 --------------> 2 Jan, 1900
365 -----------> 30 Dec, 1900 (1900 had 366 days, so one less = 30 Dec)
36525 -------> 31 Dec, 1999 (Year = 365.25 days, so 100 years = 36525 days)
Excel stores time as a decimal fraction of a day, so that 0.5 is half of a day, or 12 hours. The date and time that John F Kennedy was killed was 12:30 pm, 22 November 1963: Excel represents this by the number 23337.521 and as expected, the decimal part of the date is just over 0.5
If cell A1 contains the date for Independence Day, 1976 the Excel function
=left(A1,2) will not return “04” – it will return the value 27, since 4-July, 1976 has the Excel number 27945. If the characters "04" are required, then the user needs to first use the Excel functions that convert between numbers and strings.
How To Change Date and Time Formats in Excel
Changing the format of a date does nothing to affect the actual contents stored in an Excel cell. Figure 3 shows that clearly. While cell A1 displays the date 22-Nov-1963, the upper section shows the cell contents fully, as 22-Nov-1963, 12:30 pm.
Figure 4 shows the steps needed to change a date format: the user needs to click on “Format”, and then “cells”. A table of options appears. Under the “Number” tab, the user may select any of the many formats available.
Useful Excel Date and Time Functions
Excel provides a series of formulas that allow the user to get information about the date and time in a cell: DAY, MONTH, YEAR, HOUR, MINUTE and SECOND return a number value for the time unit requested. An error will be returned for invalid dates. For example, if 31-Apr-2000 is entered into cell A1, it will be assumed to be text, as it is not a valid date, and the Excel formula
=MONTH(A1) will not return the value “4”, but will display #VALUE!
The function INT, which takes the integer part of a number and deletes the decimal fraction, may be used to change a date and time into just a date. For example, if cell A1 contains 22-Nov-1963 12:30:00, the formula
=INT(A1) will return 22-Nov-1963 00:00:00
This function is very useful when making a Pivot Table from dates, as it is usually more useful to summarise by every day, and not by every second of every day. Sometimes it is useful to extract the time from a date and time, in which case the formula is:
=A1 - INT(A1)
The Excel function WEEKDAY is often useful when trying to analyse events by the day of the week that they happened on. The formula returns a number between 1 and 7.
Summary of Excel Date and Time Functions
Microsoft Excel stores dates as a number, and if time is included in the date then a decimal fraction is used. The base date for Excel is 1-Jan, 1900. It provides many different formats and formulas. Changing the format of a cell does affect the value stored in the cell. More information about Excel functions is available free on-line, or in Excel for Dummies by Greg Harvey.
 |
 |
 |
 |
