Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Calculate Duration Based On Start & End Times

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Calculate Duration Based On Start & End Times

    I have a spreadsheet with 4 columns - Start Date, Start Time, End Date and End Time. In the 5th column, I need to fill in the "Duration" which is calculated as follows :-

    Duration = (End Date,End Time) - (Start Date,Start Time)

  • #2
    Re: To Subtract Columns With Date/time Entries

    Good afternoon

    This formula may work for you

    =IF(D2>B2,DAY(C2)-DAY(A2)&" Days and " & HOUR(D2-B2)&" hours",(DAY(C2)-DAY(A2))-1&" Days and "&(24-HOUR(B2)+HOUR(D2))&" hours")

    I've attached an example document below
    Attached Files

    Comment


    • #3
      Re: To Subtract Columns With Date/time Entries

      How do you want to show the duration?

      To show it in hours

      =D2+C2-B2-A2

      format as [h]:mm

      you could use the same formula and format the cell to show days, hours and minutes by formatting as

      d:hh:mm

      but this won't give you correct results if you have durations above 31 days. In that situation try:

      =INT(D2+C2-B2-A2)&" days "&TEXT(D2+C2-B2-A2,"h:mm")

      Comment


      • #4
        Re: Calculate Duration Based On Start & End Times

        i used this formula but my problem now, i have several sheets using this formula, i want to calculate the total number of duration of all sheets. please help! :-)

        Comment

        Trending

        Collapse

        There are no results that meet this criteria.

        Working...
        X