+ Reply to Thread
Results 1 to 8 of 8

Adding Calculated Times and Formatting

  1. #1
    Registered User
    Join Date
    06-02-2018
    Location
    Houston, TX
    MS-Off Ver
    2016
    Posts
    3

    Adding Calculated Times and Formatting

    Hello everyone. I'm having a bit of trouble using the custom format function in Excel 2016. In a nutshell what I want is to be able to enter a time in the format hh:mm:ss:000, and add to that another time in the same format, and get the result. For example, I would like Excel to perform the following calculation:

    12:34:56.789 + 1.234 = 12:34:58.023

    I created a spreadsheet (attached) in an attempt to accomplish this but have thus far failed miserbaly. I have searched for "custom formatting time" but haven't been able to find the solution.

    In my spreadsheet, I'd like to take the time entered into cell B6, add to it the seconds displayed in cells C10, C11, C12..., and display the results in D10, D11, D12...

    So in my attached spreadsheet the result displayed in cell D10 should be, 00:10:59.457, but as you can see it is not.

    I hope that's enough information. Thanks in advance for any help you can provide.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,591

    Re: Adding Calculated Times and Formatting

    Hi, welcome to the forum

    to begin with, looks like you are referencing the wrong cell in the calc? C$6+C10 instead of B$6+C10

    then, you are adding 1 1/2 DAYS to that.

    Not sure what you were trying to do, but what you need to understand about dates and times in excel is…

    a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Sat 02 Jun 2018) is actually 43253

    Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

    So adding 1.5 to any time is actually adding 36 hours (1.5 days) to that time.

    The next thing you need to do is format it correctly to show how you want it to look. Try this...
    [h]:mm:ss;@
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-02-2018
    Location
    Houston, TX
    MS-Off Ver
    2016
    Posts
    3

    Re: Adding Calculated Times and Formatting

    Quote Originally Posted by FDibbins View Post
    to begin with, looks like you are referencing the wrong cell in the calc? C$6+C10 instead of B$6+C10
    Good catch. That was a stupid typo. Thanks.

    Quote Originally Posted by FDibbins View Post
    So adding 1.5 to any time is actually adding 36 hours (1.5 days) to that time.
    This was the piece of info I was missing. I converted 1.5 seconds to days like this 1.5/(60*60*24). That solved my issues.

    Thanks for the speedy solution. Have a nice day!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,591

    Re: Adding Calculated Times and Formatting

    Awesome, happy to help

    You could also have entered the second in time format 0:00:15

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,031

    Re: Adding Calculated Times and Formatting

    Ford is correct. If I understand, I made three edits to your spreadsheet that I think will make it do what you want:

    1) Enter 0:0:5 into B5 so that Excel will recognize this as 5 seconds and properly convert to the appropriate serial number. As Ford explained, this number is just what fraction of a day is 5 seconds, so I could have also entered =5/24/60/60
    2) Then I corrected the formula D10 to reference B$6 as Ford pointed out.
    3) Then I formatted all of the time cells to elapsed seconds [ss].000. You could also use the elapsed hours ([h]:mm:ss) format that Ford suggested, or even elapsed minutes [mm]:ss.000
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,591

    Re: Adding Calculated Times and Formatting

    oops you said 1.5 seconds, not 15 seconds...
    0:00:1.5

    @ MrShorty, thanks for the backup

  7. #7
    Registered User
    Join Date
    06-02-2018
    Location
    Houston, TX
    MS-Off Ver
    2016
    Posts
    3

    Re: Adding Calculated Times and Formatting

    Quote Originally Posted by MrShorty View Post
    Ford is correct. If I understand, I made three edits to your spreadsheet that I think will make it do what you want:

    1) Enter 0:0:5 into B5 so that Excel will recognize this as 5 seconds and properly convert to the appropriate serial number. As Ford explained, this number is just what fraction of a day is 5 seconds, so I could have also entered =5/24/60/60
    2) Then I corrected the formula D10 to reference B$6 as Ford pointed out.
    3) Then I formatted all of the time cells to elapsed seconds [ss].000. You could also use the elapsed hours ([h]:mm:ss) format that Ford suggested, or even elapsed minutes [mm]:ss.000
    Thanks Mr. Shorty. Armed with the new understanding that Excel represents time in days, and the changes you made, my spreadsheet is working the way I intended.

    Quote Originally Posted by FDibbins View Post
    oops you said 1.5 seconds, not 15 seconds...
    0:00:1.5
    I knew what you meant. Thanks for the clarification though.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,591

    Re: Adding Calculated Times and Formatting

    Great, thanks for the feedback

+ 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. [SOLVED] Adding Calculated Field to PivotTable
    By Muse83 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-24-2017, 12:06 PM
  2. Keep Calculated Column Next to Pivot When Adding fields
    By beckms in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 05-23-2014, 04:46 PM
  3. Adding % Change Calculated Field
    By lj8675309 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-03-2013, 03:05 PM
  4. [SOLVED] Adding time from calculated cells
    By Russell Dawson in forum Excel General
    Replies: 4
    Last Post: 05-09-2012, 04:13 AM
  5. [SOLVED] Comparing Calculated Times
    By Mike in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-02-2006, 11:35 PM
  6. Pivottable adding calculated field?
    By Acro in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-15-2006, 12:11 PM
  7. Replies: 4
    Last Post: 11-18-2005, 01:35 PM

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