Working with Time in Excel

Excel can work with Time very easily. With Time excel can perform many formatting and calculations based time. Let us have a look into this. In this excel tips we will learn few tasks we can perform on Excel with time formats.

Typing Time in Excel Worksheet

When time is entered into worksheet it should be entered with a colon between the hour and the minutes, such as 12:30, rather than 12.30.Excel can cope with either the 24hour system or the am/pm system.To use the am/pm system you must enter the am or pm after the time.You must leave a space between the number and the text.
Example for writing Time in 24 hour format:
01:30, 2:30, 20:10, 23:30 etc
Example for writing Time in AM/PM format:
1:30 AM, 2:30 AM, 8:10 PM, 11:30 PM etc

Calculation of Time differences in Excel

:
You can add or subtract the two times to get know the length of the time or add the two time times to get total time.
Example for Subtracting two Times in Excel:

Row/Column A B
1 Start Time End Time
2 1:30 AM 2:30 AM
3 08:00 17:00

Now you can use formula like this to find difference between the two times.Enter  =A2-B2 in the required result cell. You will get 1:00. And the second one will give you 9:00(A3-B3).
Example for Adding two times in Excel:

Consider above table. Adding is very easy. Enter formula =A2+B2 which returns 04:00 and the second row will return 25:00:00. Some time it just returns 1 hours. This is because of the cell formatting. Let us look into the   time formatting.

Formatting of Time in Excel:
Considering above example second row 3. Sum goes beyond 24 hours. The general time format will show only the time excluding the number of days. For correcting this,  easiest way is to opt for Custome formatting.
Applying Custom Formatting to Time to show exact value:
Time Formatting

  • Click on the cell which needs the format.
  • Choose the Format menu.
  • Choose Cells.
  • Click the Number tag at the top right.
  • Choose Custom.
  • Click inside the Type: box.
  • Type [hh]:mm as the format.
  • Click OK to confirm.hoose Custom.
  • Click inside the Type: box.
  • Type [hh]:mm as the format.
  • Click OK to confirm.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.