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
|9:00 AM||6:00 PM|
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.
|9:00 AM||6:00 PM||09:00|
Formula to be used is as below:
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:
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.
Ashfaq is Passionate about new ideas, innovation and challenges.Regarded as a Problem Solver, he believes in Sharing Knowledge.
Latest posts by Ashfaq Ahmed (see all)
- How to make an Effective Presentation in front of audience? - April 3, 2016
- More Super Market Store – Shaikpet - April 3, 2016
- Time Calculations in MS Excel - April 3, 2016