Leading the way in Microsoft Office Development

Step 1 - Entering Valid Times

In Excel times are represented as decimal fractions.

Working with times is very similar to working with dates except that times are represented as fractions or decimals.

You can enter times by excluding a date serial number to include decimals.

If you want to enter a time which is not associated with any particular date then use a zero in front of your decimal.

Step 2 - Displaying Times

The table below shows some times with their corresponding serial numbers.

Useful periods of time

If your calculation produces a negative time value, then a series of pound signs is displayed (??)

Step 3 - Default Formats

When you enter a time into a cell a time format will be applied automatically.

The time number format will only be changed if the cell contains the "general" number format.

Step 4 - Entering Invalid Times

If you enter an invalid time then it is interpreted as text and will be aligned to the left.

When you enter a time without any date associated with it Excel automatically assigns the date serial number 0.

You can enter a date and time by entering a recognised date format, followed by a space, followed by a recognised time format.

When you enter a time that is greater than 24 hours, the associated date will be incremented accordingly adding an additional day for every 24 hours.

If you enter a time that exceeds 9099:59:59 then the value is interpreted as text (and is left aligned).

Step 5 - Displaying more than 24 Hours

Step 6 - Manipulating Times

If you want to use the dates and numbers that have text formatting you can still use them in calculations. First select an empty cell and copy it. Select (Edit > Paste Special) and check Add on the cells containing dates. Does this change the actual dates ??

To fill a series of cells with a date sequence, enter the first date and drag the black square in the bottom right corner with the left mouse button.

To fill a series of cells with a date sequence (perhaps just weekdays or months) drag the black square with the right mouse button.

Place a date in a cell, move your mouse pointer over the bottom right corner of the cell (Fill Handle), drag it down with the right mouse button to display the shortcut menu.

Step 7 - Time Fractions

12:00 AM to 11:30 AM and 12:00 PM to 11:30 PM

Step 8 - Things to Remember

You can enter the current time as a decimal fraction by typing "=NOW()-TODAY()"

You can combine dates and times in the same cells.