+ Reply to Thread
Results 1 to 10 of 10

Add current custom time hh:mm:ss.00 with second and fraction of seconds

  1. #1
    Registered User
    Join Date
    11-16-2021
    Location
    York, England
    MS-Off Ver
    2013
    Posts
    3

    Add current custom time hh:mm:ss.00 with second and fraction of seconds

    Hi

    I want to add 17.22 seconds to the current time in custom format ie hh:mm:ss.00

    eg lets say the current time is : 15:59:30.17, I would like a formula to add 17.22 seconds to it in this case the result would be
    15:59:47.39 What's the formula to do this?

    Also

    I then have a column full of times base on the calculation and would need to find the earliest time of this range.

    What would be the best approach, I've been trying the SMALL() or MIN() function. Will this work or is there a beater way.

    Cheers

    Chris

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Add current custom time hh:mm:ss.00 with second and fraction of seconds

    Maybe
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Add current custom time hh:mm:ss.00 with second and fraction of seconds

    SMALL(range, 1) or MIN(range) should both work.

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

    Re: Add current custom time hh:mm:ss.00 with second and fraction of seconds

    It's not clear exactly what you are working with.

    Assuming everything is already in Excel's date time serial number system (so all times are stored as fraction of a day), then it should be as simple as current time + fraction of a day representing 17.22 seconds.

    If you are starting with the current time in Excel's date/time serial number system (stored as a fraction of a day) and the 17.22 is in decimal seconds, then you simply need to convert the 17.22 seconds to its corresponding fraction of a day value and then add them together =currenttime+CONVERT(17.22,"sec","day")

    If any of this is stored as text rather than number, convert the time/numbers stored as text to real numbers, then add them up.

    Does any of that help?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    11-16-2021
    Location
    York, England
    MS-Off Ver
    2013
    Posts
    3

    Re: Add current custom time hh:mm:ss.00 with second and fraction of seconds

    Hi

    Thanks for your replies

    This =cell + TIMEVALUE("00:00:17.220") works for a static value but I'm taking the value from a calculation Distance (in m)/Speed (m/s) of 2 difference cells. Any ideas, my current formula that does not seem to work is : =NOW() + TIME(0, 0, ('Distance'!AG14/'Speed'!AH14))

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Add current custom time hh:mm:ss.00 with second and fraction of seconds

    Please see the yellow banner at the top of the page. We can't guess at what you're looking at.

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

    Re: Add current custom time hh:mm:ss.00 with second and fraction of seconds

    A quick test suggests that the TIME() function can only receive integers for its arguments (any fractions are truncated). Did you try my suggestion? =NOW()+CONVERT('Distance'!AG14/'Speed'!AH14,"sec","day") That should correctly convert your time (assuming it's in seconds) to days (the base unit for Excel's date/time serial number system), then add the result to the date/time serial number at the moment the calculation event occurs.

  8. #8
    Registered User
    Join Date
    11-16-2021
    Location
    York, England
    MS-Off Ver
    2013
    Posts
    3

    Re: Add current custom time hh:mm:ss.00 with second and fraction of seconds

    =NOW()+CONVERT('Distance'!AG14/'Speed'!AH14,"sec","day") seems to do the trick, thank you very much

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Add current custom time hh:mm:ss.00 with second and fraction of seconds

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  10. #10
    Valued Forum Contributor
    Join Date
    07-13-2021
    Location
    California, USA
    MS-Off Ver
    2010
    Posts
    513

    Re: Add current custom time hh:mm:ss.00 with second and fraction of seconds

    Quote Originally Posted by ChrisF1 View Post
    I want to add 17.22 seconds to the current time in custom format ie hh:mm:ss.00
    Quote Originally Posted by ChrisF1 View Post
    =NOW()+CONVERT('Distance'!AG14/'Speed'!AH14,"sec","day") seems to do the trick
    The operative word might be "seems".

    First, when doing time arithmetic, it is prudent to use the format [hh]:mm:ss.00. The squared-bracketed "[hh]" displays time values of 24 hours or more.

    Second, Excel NOW includes the current date with current time. If you want the current time only, you might write MOD(NOW(),1) .

    (FYI, Excel NOW is rounded down to a 10-millisecond multiple.)

    Third, for a more succinct formula, you might write =MOD(NOW(),1) + 'Distance'!AG14/'Speed'!AH14/86400 , reflecting the fact that there are 86400 sec in a day.

    Finally, all of these calculations might introduce infinitesimal differences from the correct binary approximation of the displayed time.

    That might be okay for your immediate purposes.

    But if you might compare the calculated time with the "same" time calculated (accurately) another way or entered manually, you might write:

    =--TEXT(MOD(NOW(),1) + 'Distance'!AG14/'Speed'!AH14/86400, "[h]:m:s.00")

    The double negate (any arithmetic) converts the text value to a numeric value.
    Last edited by curiouscat408; 11-16-2021 at 05:38 PM. Reason: cosmetic

+ 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. Replies: 6
    Last Post: 10-08-2011, 08:16 PM
  2. Replies: 6
    Last Post: 12-22-2010, 07:23 AM
  3. Filtering time from 1 seconds into 10 seconds
    By Deane in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-30-2010, 09:48 PM
  4. Fraction of seconds
    By ArthurJ in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-16-2006, 04:50 PM
  5. Replies: 2
    Last Post: 10-04-2005, 10:05 AM
  6. [SOLVED] Code to show current hour minute and seconds in a cell
    By Dr.H.Subramanian in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-11-2005, 07:05 AM
  7. Replies: 7
    Last Post: 05-08-2005, 04:06 PM
  8. Replies: 3
    Last Post: 04-12-2005, 07:07 PM

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