Time Calculations in MS Excel

MS Excel is a widely used spreadsheet document provided by Microsoft on windows.
Excel is handy when it comes to calculations. Let’s learn about the Time Calculations in Excel.(If you are new to excel, recommend you to Learn Excel Basics first)

Excel can work with time very easily. Time can be entered in various different formats and calculations be performed.

How do you enter the time in Excel?
When time is entered in the worksheet, it should be entered with a colon (“:”) between the hours and minutes like 2:10 unlike 2.10

Excel can understand both the 12H and 24H time system. even the am/pm system. Lets look at some examples below

Start End
9:00 AM 6:00 PM
10:00 19:00

Now that you have understood the various ways of entering the time in worksheet, you may want to do some calculations on that. Let’s work as below:

Find the Difference/Duration between two times
You can find the time duration between 2 times, by subtracting them. Subtract the Start time from the End time.

Start End Duration
9:00 AM 6:00 PM 09:00
10:00 23:30 13:30

Formula to be used is as below:

 

Duration
=B3-A3
=B4-A4

Note: If you just subtract the cells without formatting you will not get the correct value. ensure you have done the custom formatting as below

Right click the selected cells and click on “Format Cells”:

In the Format Cells window, select “Custom” on the left pane and put “hh:mm” under the “type” as shown in the figure below. and click “OK”

 

Find the End time by Adding time duration

You can add the time to find the total time. for example, you can find out what time will you reach a destination if the travel takes x amount of time. Check the example below which shows the reach time based on your start time and the travel duration due to traffic.

Start From Home Travel Time ( in hours) Reach College
6:00 AM 0:30 6:30 AM
7:00 AM 0:50 7:50 AM
8:00 AM 1:15 9:15 AM
8:15 AM 1:45 10:00 AM

Formula Used here:

Reach College
=A8+B8
=A9+B9
=A10+B10
=A11+B11

What we did is a simple Arithmetic Addition.

Key point to note here is to remember how you enter the time. Use Semicolon to make Excel understand that its a time parameter.

Happy Learning

Ashfaq Ahmed
Follow me...

Ashfaq Ahmed

Ashfaq Ahmed is an experienced Professional in Computers & Senior Management. He has dual Masters in Business Administration and Computer Sciences.
Ashfaq is Passionate about new ideas, innovation and challenges.Regarded as a Problem Solver, he believes in Sharing Knowledge.
www.ashfaqahmeds.com
Ashfaq Ahmed
Follow me...

Leave a Reply

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