Objectives

· Understand how dates and times are represented internally in Excel

· Format dates

· Use the Today() function to get the current date

· Use the Now() function to get the current date and time

· Use the Date() function convert a month, day, and year into an Excel date (serial number)

· Use the Month(), Day(), Year(), and WeekDay() functions to extract individual fields from an Excel date

How Excel Stores Dates And Times

Excel stores dates using a serial number. The serial number 1 is January 1, 1900, serial number 2 is January 2, 1900, etc. January 1, 2000, is 36,526 (note that this is 1 + 365 days * 100 years + 25 leap year days.

Times are stored as a fraction of a day. For example, .5 is half a day, or 12:00 noon. .75 is three-fourths of a day, or 6:00 p.m., etc.

Dates and times are combined – the integer part of a serial number represents the date, and the fractional part of a serial number represents the time of day. So 12 noon on January 1, 2000 is represented as 36,526.50.

Entering two-digit years in Excel

You should always enter years as 4-digit numbers (entering years as 2-digit numbers was the cause of the "Year 2000 problem"). However, if you enter the year portion of a date as a 2-digit number, you need to know how Excel will interpret it. Excel interprets two–digit dates like this:

· 30-99 – are treated as part of the 20^th century. That is, the digits "19" are put on the front of the number.

· 0-29 – are treated as part of the 21^st century. The digits "20" are put on the front of the number.

Entering a date/time in a cell

To enter a date in a cell, simply type the date in the format mm/dd/yyyy. Use slashes to separate the month from the day and the day from the year. If you use any other separator character, it will look correct, but Excel will not represent it internally as a date and you will not be able to perform date arithmetic on it.

To enter a time in a cell, simply type the time in the format hh:mm:ss. Append either AM or PM to the end of the time. The seconds field (ss) is optional. You must have a blank between the time and the AM or PM. Note that if you do not enter a date before the time, the number 0 will be to the left of the decimal point and the date 1/0/1900 will be assumed (and while this is not a valid date, it does not cause a problem)!

Date arithmetic

You can determine how much time has passed between two dates simply by subtracting them. Since Excel stores dates as serial numbers, subtracting two dates will give you the number of days from the first date to the second date. Note that Excel may sometimes format these numbers incorrectly; sometimes it will try to format the difference between two dates as a date, which will give a strange-looking result.

The current date: TODAY()

To get the current date to always appear in a cell, enter the today() function in the cell. Note that the today() function has no arguments; however, the parentheses are still required.

The current date and time: NOW()

To get both the current date and the current time of day to appear in a cell, enter the now() function in the cell. The now() function also has no arguments.

Extracting the month, day, day of week, or year from a serial number: MONTH(), DAY(), WEEKDAY(), YEAR()

There may be times when you have a date (as a serial number) and you want to extract one of its fields: the month, the day, or the year. Excel has three functions for extracting these values.

· The month() function requires a single argument – a serial number representing a date. Example: The formula month(today()) will return the number of the current month (1=Jan, 2=Feb, etc.)

· The day() function requires a single argument – a serial number representing a date. Example: The formula day(today()) will return the number of the current day.

· The year() function requires a single argument – a serial number representing a date. Example: The formula year(today()) will return the current year.

· And the weekday() function also requires a single serial number representing a date. It will return a number representing the day of the week, where Sunday is day 1, Monday is day 2, etc. Example: The formula weekday(today()) will return a number representing the current day of the week (1=Sun, 2=Mon, etc.)

Examples:

July 20, 1969 (the day of the first moon landing) is represented by the serial number 25,404.

So the formula month(25404) or month(7/20/1969) will return 7 (July).

The formula day(25404) will return 20.

The formula year(25404) will return 1969.

The formula weekday(25404) will return 1 (Sunday).

Converting a month, day, and year into an Excel date: DATE(year, month, day)

If you already have a spreadsheet with the dates spread out over three columns (a month column, a day column, and a year column), you may want to convert those three values into a single date serial number (so you can use it in date arithmetic calculations later). To do so, use the Date() function. The Date() function requires three arguments:

Date(year, month, day)

Note that they are not in the usual m/d/y format that we are used to, but are in decreasing order.

Example:

Entering the formula, before pressing the Enter key:

After pressing the Enter key:

Computing elapsed hours

You can use Excel to compute elapsed hours (e.g. to compute number of hours worked when given the time a person clocked in and clocked out). To do so, enter the start time and end time in separate cells. Subtract the start time from the end time. This will give you the fraction of a day that has elapsed. Multiply this by 24 to get the number of hours.

Example:

A person clocks in at 6:00 am, and clocks out at 12:00 pm (noon). 6:00 am is represented as .25 (a fourth of a day has elapsed by 6:00 am), and 12:00 pm is represented as .5 (half a day has elapsed by 12:00 pm). The amount of time between 6:00 am and 12:00 pm is .5 - .25 = .25, which is a fourth of a day. Multiply .25 times 24 hours, and you get 6 hours.