• Home
  • Blog
  • Courses
  • Tools
  • Jobs
  • Forums
Have any question?
(00) 201277767728
[email protected]
RegisterLogin

Login with your site account

Lost your password?

Not a member yet? Register now

Register a new account

Are you a member? Login now

Planning Engineer Est.
  • Home
  • Blog
  • Courses
  • Tools
  • Jobs
  • Forums
      • Cart

        0

    Excel in Planning

    • Home
    • Blog
    • Excel in Planning
    • How to calculate the difference between two times in Excel

    How to calculate the difference between two times in Excel

    • Posted by Hany Ismael, Msc, PMP
    • Categories Excel in Planning
    • Date August 23, 2016
    • Comments 0 comment

    Problem

    If you have an Excel sheet with two columns “Check In” and “Check Out” in Time Format also and want to calculate the working hours between the two values which can be calculated with the following formula:

    Working Hours = Check Out Time – Check in Time

    1- What you want to do

    2- Result


    You can find the result is different than you want, the expected correct answer is

    (8 Hours), which is different than what appeared in the result photo

    Our Solutions:

    Excel has a number of formulas that enable you to count the time between two dates. You can specify that you would like to count the difference in hours by adding a time specified to your formula or by altering the cell format in your spreadsheet. Excel also enables you to apply your formula to multiple cells simultaneously by clicking the corner of your primary formula cell and dragging it with your mouse. As long as you have a start time and an end time, Excel will be able to count the hours between them.

    Solution 1:

    You can use Text function to change the format , also you can specify the result format which you want to get “h:mm” – “h” stands for “Hours” – “mm” stands for “Minutes”


    You can also use one of the following formula

    Formula

    Description

    Text Function

    =TEXT(E3-D3,”h”)

    Hours between two times with the cell formatted as “h”

    =TEXT(E3-D3,”h:mm”)

    Hours and minutes between two times with the cell formatted as “h:mm”

    =TEXT(E3-D3,”h:mm:ss”)

    Hours, minutes, and seconds between two times with the cell formatted as “h:mm:ss”

    Solution 2:

    You can use INT function to change the format, also you can specify the result format which you want to get as (Hours
    – Minutes – Seconds).


    Note: in this solution you will neglect the frictional part of hour

    Formula

    Description

    INT Function

    =INT((E3-D3)*24)

    Total hours between two times

    =INT((E3-D3)*1440)

    Total minutes between two times

    =INT((E3-D3)*86400)

    Total seconds between two times (17700)

    Solution 3:

    You can use on of the following functions directly

    Formula

    Description

    =HOUR(E3-D3)

    The difference in the hours unit between two times.

    =MINUTE(E3-D3)

    The difference in the minutes unit between two times

    =SECOND(E3-D3)

    The difference in the seconds unit between two times.

    Tag:calculate difference times

    • Share:
    author avatar
    Hany Ismael, Msc, PMP

    Hany Ismael is the founder and CEO of Planning Engineer Est. in Egypt. He has started his career back in 2003 as a site engineer, technical office engineer, planning engineer, planning manager, and finally planning department manager where he has been involved in several mega construction projects in Egypt and Saudi Arabia. In 2016, he established his own company in Egypt “Planning Engineer Est.” Hany gained his MSc degree in project management from Liverpool University-UK 2013-2016, PMP certified from PMI-USA 2010, and BSc Civil Engineer Tanta University-Egypt 2003. Hany provided more than 3,500 hours of planning and project management training on his website planningengineer.net, YouTube channel, and offline courses since 2011. He enjoys teaching project management in simple and practical way, and he developed several planning tools, techniques and courses.

    Previous post

    Be focused if you want to be successful Planning Engineer
    August 23, 2016

    Next post

    Changing PC name will make you unable to Connect to Primavera Database
    August 23, 2016

    You may also like

    Materials Procurement Scheduling in a Baseline Programme
    Materials Procurement Scheduling in a Baseline Programme
    7 May, 2022
    Planned VS Actual Excel Sheet
    Mini Schedule Planned VS Actual Excel Sheet
    18 August, 2021
    3 weeks look ahead schedule
    3 weeks look ahead schedule excel sheet
    24 July, 2021

    Leave A Reply Cancel reply

    You must be logged in to post a comment.

    Cart

    0

    Planning Courses

    Cost Control for Construction Projects

    Cost Control for Construction Projects

    $299.00
    Power Bi Monitoring and Controlling with P6

    Power Bi Monitoring and Controlling with P6

    $299.00
    Advanced Planning 3 Courses Package

    Advanced Planning 3 Courses Package

    $499.00
    Data Management and Reporting

    Data Management and Reporting

    $199.00
    Primavera Online Basic Course -Arabic

    Primavera Online Basic Course -Arabic

    $99.00
    Professional Planning Engineer (PPE)

    Professional Planning Engineer (PPE)

    $335.00
    Claims Management and (EOT)

    Claims Management and (EOT)

    $175.00
    Primavera Online Basic Course -English

    Primavera Online Basic Course -English

    $99.00

    Follow Us:

    About Us | Contact Us | Terms of Service | Privacy Policy | Refund Policy

    Copyright ©2012- 2018 planningengineer.net | All materials, unless otherwise noted, were developed originally for Planning Engineer Website. In the event that there is still a problem or error with copyrighted material, the break of the copyright is unintentional and noncommercial and the material will be removed immediately upon presented proof.

    • Planning Courses
    • Planning Jobs
    • Planning Blog
    Posting....