+ Reply to Thread
Results 1 to 17 of 17

Custom Formatting Time Duration & Editing A Time Calculation

  1. #1
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    526

    Custom Formatting Time Duration & Editing A Time Calculation

    Hello,

    I have a list of times over two tabs. I am trying to configure a formula to display the shown results and also custom format them as well. Up until now, both instances I’m only 5 minutes off.

    I’m aware that seconds may be involved but are unknown unless given like the two Gray cells. Though first noticed in tab 2, when two times are 2 hours but one is displayed as “1hs 59mm” and the other “2hs.” The yellow highlight is to resemble this difference.

    The orange cells are what is shown, but I’m not sure why compared to the simple subtraction formula.

    I’m not sure if a custom format can be created as shown and briefly explained in the tab 1.

    Any assistance would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    3,927

    Re: Custom Formatting Time Duration & Editing A Time Calculation

    Try this custom formatting:
    [<0.0416666666666667]m"mm" s"ss";[>=0.0416666666666667]h"hs "m"mm"

  3. #3
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Custom Formatting Time Duration & Editing A Time Calculation

    It is helpful. For the whole hours, would this make sense? [<>1/1440]”hs”?

  4. #4
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Custom Formatting Time Duration & Editing A Time Calculation

    Update: I’ve tried the calculations separately. When I go to insert in Custom Format it won’t let me use the third and I am using “;”.

    1. [<0.0416666666666667]m”mm” s”ss”
    2. [=0.0416666666666667]h”hs”
    3. [>0.0416666666666667]h”hs” m”mm”

    07:00AM 07:59AM - 59mm 0ss
    07:00AM 09:00AM - 2hs
    07:00AM 08:01AM - 1hs 1mm
    Last edited by Dexter2; 01-26-2024 at 10:45 AM.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,961

    Re: Custom Formatting Time Duration & Editing A Time Calculation

    If you are using a European locale, change decimal points to decimal commas, e.g. <0,0416666666666667
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  6. #6
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Custom Formatting Time Duration & Editing A Time Calculation

    In both instances, this dialogue box appears:

    “Microsoft Excel
    Microsoft Excel cannot use the number format you typed.

    Try using one of the built-in number formats.

    For more information about custom number formats, click Help.”

    If I remove the third calculation, then it works.

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

    Re: Custom Formatting Time Duration & Editing A Time Calculation

    I potentially see two very different questions, and I'm not sure which question to focus on:

    1) The "auditing" question that is trying to understand whether the calculations are correct and trying to understand where different sources come to different results. When I want to "audit" a calculation like this, I tend to ignore the number formatting questions and format the numbers to show as much information as possible. In this case, that means a number format that shows out to milliseconds like hh:mm:ss.000 or [hh]:mm:ss.000. I would not expect floating point errors to contribute more than 1 second of error, so any calculations that are different by more than a second (the errors you are highlighting are 1 or more minutes) represent a difference in calculation scheme. As near as I can tell, Excel's calculations are correct for the inputs and calculation scheme given. As with past, similar conversations on these topics, we on this side of the internet have no way of auditing the "outside" numbers you've entered. I expect that about all we can do is affirm that Excel knows how to add and subtract numbers correctly, and that the "errors" that you point out are due to different calculation schemes.

    2) The number formatting question that wants to apply 3 different number formats based on 3 conditions. If I understand correctly, I don't think you will succeed using basic number formatting. I think you will need to use full conditional formatting where you use a formula to test for each condition and apply the appropriate number format. If you need help with conditional formatting, start here: https://support.microsoft.com/en-us/...b-f1951ff89d7f Let us know if you have trouble putting the formulas together or applying the formats.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  8. #8
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    526

    Re: Custom Formatting Time Duration & Editing A Time Calculation

    Thank you for your feedback.

    1) After further work on this and receiving official assistance from employer, how do you make the decimals zero? Example:

    528pm 805pm = 2:37 but converted to decimal it’s 2.61666666666667 (cell-cell)*24. How do I convert it instead to 2.6200000000?

    I have tried ROUND, INT, MOD functions.

    2) Need more time for the Conditional Formatting.
    Last edited by Dexter2; 02-26-2024 at 02:44 PM.

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

    Re: Custom Formatting Time Duration & Editing A Time Calculation

    Rounding a decimal hours number to the nearest 1/100th of an hour should be as easy as ROUND(decimal hour calculation,2).

  10. #10
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    526
    Quote Originally Posted by MrShorty View Post
    Rounding a decimal hours number to the nearest 1/100th of an hour should be as easy as ROUND(decimal hour calculation,2).
    I tried that initially, and it doesn’t seem to work for each row. It rounds it, but when expanded the decimals aren’t zeros which still equals a different SUM than expected.

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

    Re: Custom Formatting Time Duration & Editing A Time Calculation

    Do you have an example (maybe in a new sample file)? I cannot recreate a case where ROUND(calc,2) fails to round to the nearest 1/100th.

  12. #12
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    526
    Having found and corrected my error. Please see attached.

    Is there a better formula i can use in cell D22? Having different number of rows would become tedious for “ROUND+ROUND”. Cell D23 came up with an incorrect result, though technically not wrong.
    Attached Files Attached Files

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

    Re: Custom Formatting Time Duration & Editing A Time Calculation

    The only possible "improvement" I could see for a "take difference, then round, then sum" calculation scheme might be a LET() or LAMBDA() function that will allow you to specify the ROUND() operation for multiple differences at once. (Note that C23 is using a "take difference, then sum, then round" scheme, showing that the the placement of the round operation can make a difference in the final result). Since my older version doesn't support either of these functions, I will let others more familiar with these suggest the proper syntax.

  14. #14
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,779

    Re: Custom Formatting Time Duration & Editing A Time Calculation

    You could use:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Everyone who confuses correlation and causation ends up dead.

  15. #15
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    526
    Thank you both your reply. The formula above seems to work. I have tried to apply it to the cells highlighted in yellow. Can you please further assist?
    Attached Files Attached Files

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

    Re: Custom Formatting Time Duration & Editing A Time Calculation

    After a few days, I will venture a response. If I understand rorya's formula, and you want to apply it to the conditional sums in G4 and G5, I would expect to simply nest the ROUND(...) part of the function inside of an IF() function that decides what to do based on column D. Maybe try =SUM(BYROW(...,LAMBDA(r,IF(INDEX(r,4)=1,ROUND((...)*24,2),0)))) [did I get the parentheses all in the right place?]. Similar for task type = 2.

  17. #17
    Forum Contributor
    Join Date
    12-25-2020
    Location
    United States
    MS-Off Ver
    Office 365
    Posts
    526
    Quote Originally Posted by MrShorty View Post
    After a few days, I will venture a response. If I understand rorya's formula, and you want to apply it to the conditional sums in G4 and G5, I would expect to simply nest the ROUND(...) part of the function inside of an IF() function that decides what to do based on column D. Maybe try =SUM(BYROW(...,LAMBDA(r,IF(INDEX(r,4)=1,ROUND((...)*24,2),0)))) [did I get the parentheses all in the right place?]. Similar for task type = 2.
    I believe you have solved it sir! Thank you very much

+ 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] Calculation of duration in hh:mm during pre-defined time range
    By infratunes in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-12-2018, 04:06 AM
  2. Time duration calculation
    By excel4jms in forum Excel Formulas & Functions
    Replies: 31
    Last Post: 01-14-2018, 12:31 AM
  3. Conditional Formatting with Time Duration...
    By fishermanryan in forum Excel General
    Replies: 1
    Last Post: 04-11-2017, 01:05 AM
  4. Calculation of Time duration in hours from 24hours input data
    By maxonline in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-04-2016, 10:51 AM
  5. Problem with calculation in a time duration formula
    By hal87 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-21-2015, 09:36 PM
  6. [SOLVED] need help with duration (cumulative time) calculation
    By hgeorges in forum Excel General
    Replies: 4
    Last Post: 07-29-2014, 04:01 PM
  7. [SOLVED] Sumproduct with Time Duration calculation
    By gav0101 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-05-2012, 06:34 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