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 (??)

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

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.

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.

Adding Times

You can add times using the "+" operator or by using the SUM function.
However Excel will not add more than 24 hours.
When the time exceeds 1 it rools to the next day
To force Excel to stay on the same day you can use on the following custom number formats:
[h]:mm:ss
'37:30:25

It is possible to add times together using the normal + operator only when the total number of hours is not greater than 24.
If the total time is more than 24 hours then this approach will not work.
If you add up 8:00, 9:00 and 10:00 using a normal formula you will get the result 3:00 which is obvously not right.
Excel defaults to displaying times in 24hr notation so (8 + 9 + 10 = 27) = 3 in a 24hr notation.

If cell A1 contains a true date and time then:
to get the date component use INT(A1)
to get the time use A1-INT(A1) or MOD(A1,1)
to find the difference between two dates use DATEDIF(A1,A2,"d")

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.