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.

When you enter a time without a date, the date automatically defaults to 0, Januray 1900.
If you are using these times in calculations you can subtract one from another to create a negative time value.
When this happens Excel will display an error, indicated by piund signs in the cell ??
One work around is to switch to 1904 date system.

Displaying Times

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

Format a cell as [m] to display the total number of minutes
Format a cell as [s] to display the total number of seconds

Useful periods of time

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

Displaying more than 24 Hours

To see the total number of hours you need to format the cell with the "[hh]:mm" number format
The brackets around the "h" prevents the 24hr notation from being applied.

24 Hour Time Format

You can display your times in 12 or 24 hour format by changing the number format.
The square brackets allow calculations over a 24hr period.
This allows to display the decimal 1.5 as 1:30.

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.

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).

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.

Time Fractions

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

Important

You can enter the current time as a decimal fraction by typing "=NOW()-TODAY()"
You can combine dates and times in the same cells.