Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Calculate Duration Based On Start & End Times

  1. #1
    Join Date
    21st January 2008
    Posts
    1

    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)

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    3rd August 2007
    Location
    Newcastle Upon Tyne, UK
    Posts
    40

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th April 2007
    Location
    England
    Posts
    675

    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")

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    29th October 2010
    Posts
    1

    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! :-)

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: February 14th, 2008, 16:06
  2. Calculate Holidays Remaing Based On Start Date
    By kevinm12 in forum EXCEL HELP
    Replies: 2
    Last Post: November 22nd, 2007, 04:16
  3. Calculate Start/End Times
    By kohoutek in forum EXCEL HELP
    Replies: 11
    Last Post: December 20th, 2006, 14:41
  4. Replies: 3
    Last Post: November 17th, 2005, 20:38
  5. Start Time & Duration to Start & End Time
    By jshayler in forum EXCEL HELP
    Replies: 6
    Last Post: November 12th, 2004, 20:35

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno