+ Reply to Thread
Results 1 to 11 of 11

Calculating Amount of Time Elapsed

  1. #1
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Richmond, VA
    MS-Off Ver
    Excel 365
    Posts
    164

    Calculating Amount of Time Elapsed

    Hello,

    I would like to calculate the time elapsed between an end date, end time, start date, and start time columns (4 columns) and have the answer show in hours and minutes. I would also like to show a blank cell if any one of these attributes is not populated in the respective column(s).

    I thought I had calculated the time elapsed using a very basic calculation with the exception of addressing the blank cell issue. However, when I create a pivot table to summarize each individual row for time elapsed, the totals are wrong. The pivot table "Sum" for 2014 for instance shows as "1/16/1900 10:47: 00 AM" in the formula bar, and as 10:47 in the pivot table, and the answer from the table when I filter the time elapsed is 394:47:00.

    I am certain that some of the issue is formatting, but have tried everything I can think of to fix that, and have run out of ideas.

    Any help would be greatly appreciated.

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Calculating Amount of Time Elapsed

    The formula would be...

    =(end date+end time)-(start date+start time)

    Format the result as [h]:mm
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Calculating Amount of Time Elapsed

    have you tried using a formatting of
    [H]:MM
    it makes the result go over the 24hrs

    for leaving blank
    assuming the cells are A2,B2,C2 & D2
    =IF( OR(A2="",B2="",C2="",D2="") , "" , The calc formula)

    otherwise
    can you upload a sample spreadsheet please
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Calculating Amount of Time Elapsed

    Quote Originally Posted by etaf View Post
    for leaving blank
    Ooops! Forgot about that part.

    =IF(COUNT(A2:D2)<4,"",(end date+end time)-(start date+start time))

  5. #5
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Richmond, VA
    MS-Off Ver
    Excel 365
    Posts
    164

    Re: Calculating Amount of Time Elapsed

    This works fine for the additional column in the Excel table in which the time elapsed is shown. However, when I insert a pivot table, the totals are incorrect and if you look in the formula bar in the pivot table for one of the months for example, something like this appears (for August):
    <1/1/1900 4:47:00 AM>
    instead of the correct SUM for the month of August which is 28:47:00
    I have tried reformatting, researching the issue, etc.

    Also, I am hoping to find out how to write the formula so that if any of the values or end date, end time, start date, and start time are not populated in the cell that Excel will leave the time elapsed cell blank.

  6. #6
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Richmond, VA
    MS-Off Ver
    Excel 365
    Posts
    164

    Re: Calculating Amount of Time Elapsed

    The =IF(COUNT(A2:D2),4 . . . formula works until I format the column to <h:mm>. The minute I update the formatting which is needed, all of the blank cells change to <##########################>

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Calculating Amount of Time Elapsed

    you should be able to format the field to show [H] as well

    Also, I am hoping to find out how to write the formula so that if any of the values or end date, end time, start date, and start time are not populated in the cell that Excel will leave the time elapsed cell blank.
    thats what the formulas posted here should do

    can you load a sample sheet here

  8. #8
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Richmond, VA
    MS-Off Ver
    Excel 365
    Posts
    164

    Re: Calculating Amount of Time Elapsed

    I actually did format the values in the pivot table as <h:mm>

    Attached is a sample from my original file
    Attached Files Attached Files

  9. #9
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Calculating Amount of Time Elapsed

    where are you using the formula ?

    the idea of a sample is to actually see where you are using it and what the format is

    would you explain what cells you are using and calculating please

  10. #10
    Forum Contributor
    Join Date
    05-01-2013
    Location
    Richmond, VA
    MS-Off Ver
    Excel 365
    Posts
    164

    Re: Calculating Amount of Time Elapsed

    If you choose the worksheet "Sheet7", that is the pivot table. Then click in cell B17 which is the "Grand Total" and see that the fx value in the ribbon is "1/16/1900 10:47:00 AM"

    There is also an issue in the table with the sample data "Sheet1" in column Q, where I use the formula posted in one of the responses that should leave blank the calculated cells where not all of the values (end date, end time, start date, start time) are not populated. The cells remain blank as long as I do not format the column with the customer h:mm, which I actually need to properly show the time.

    Sorry - my intention is to make this as clear and simple as possible.

  11. #11
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Calculating Amount of Time Elapsed

    you need to format the number on the field selection NOT on the sheet itself

    see attached now using [H]:HH

    right click on the field list
    Value Field Settings
    Number format
    change there


    I have also added in column Q the formula and cleared the cells in row 2 - to check it works

    I assume you meant Column P as Q was empty
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculating elapsed time
    By Ladyrose722 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-13-2013, 04:42 AM
  2. Calculating elapsed time from Max day in row
    By iamconstance in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-20-2013, 09:27 AM
  3. Elapsed time & Amount due
    By ABcdn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-22-2012, 07:57 PM
  4. Calculating Time Elapsed
    By ALBCBSCT in forum Excel General
    Replies: 2
    Last Post: 09-30-2010, 10:24 AM
  5. Calculating Elapsed Time
    By ExcelUser55 in forum Excel General
    Replies: 4
    Last Post: 04-15-2008, 03:59 PM
  6. Calculating elapsed time
    By tele2002 in forum Excel General
    Replies: 2
    Last Post: 12-22-2005, 01:50 PM
  7. Calculating Time elapsed
    By bhomer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-21-2005, 09:20 AM
  8. [SOLVED] Calculating elapsed time
    By andoh in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-17-2005, 07:35 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1