# Discussion on: Three tips for rounding Excel time values

20

### Join the conversation!

View:
Show:
###### (\$C2-\$B2+(\$B2>\$C2))
Can you explain the (\$B2>\$C2) section. ?
###### Crossing Midnight
mckinnej 17th Aug
I believe that's for when you cross midnight. Take out the +(\$B2>\$C2) parts and watch what happens to the 3/19 values. (Spoiler: It blows up.)
Contributr
###### Exactly
ssharkins@... 17th Aug
It's a simple trick that allows one simple formula to handle time values that don't fall within the same 24-hour day.
###### Crossing Midnight
DaveUnger 20th Aug
Thanks for showing me that trick Susan. Great article as always.
Contributr
###### Thanks!
ssharkins@... 22nd Aug
###### Can you explain the (\$B2>\$C2) section. ?
CNSHAW 17th Aug
The (\$B2>\$C2) section evaluates a logical expression (TRUE or FALSE) which equates to 1 or 0 when combined with the rest of the numerical expression. If you add zero to any logical expression (e.g. =(A1>A2)+0) it will evaluate to one or zero.

Thus, this is included to accommodate times crossing midnight as the start time will be greater than the finish time and result in a negative number in the expression. A value of one (24 hours in Excel time format) is therefore added to the remainder of the expression to recognize the finish time is "next day" and produce a positive outcome for displaying the time.
###### Excel Tips
rsdance@... 17th Aug
Another excellent article. I have learned a lot from your posts and really appreciate them.

Rupert
Contributr
###### Thank you!
ssharkins@... 17th Aug
Thank you Rupert -- I'm glad you're finding the Office posts helpful!
###### Excel tip
trs789@... 17th Aug
What is the format for Option 1 (F2)? Thanks.
###### Excel Tip
trs789@... 17th Aug
Can anyone give me any other examples of when you'd use these functions other than timekeeping? Thanks.
Contributr
###### Rounding
ssharkins@... 17th Aug
Although the focus of the article is rounding time, you could easily use the rounding functions discussed in any formula that required rounding. Knowing the three different functions should prove useful, even if you don't need the timing aspect -- hope so anyway!
###### (\$B2>\$C2) - a bit more explanation please
I'd appreciate a more detailed explanation as to how this works - if you copy it into a cell as a formula (with and = first) you get true or false but I don't understand how it works in the formula. Thanks.
1 Vote
###### Remove time stamp from date
Smilodon 17th Aug
These have been a series of great articles. I have a different issue where I need to remove the time value from the date. The most straightforward way I have found to remove the time stamp from the date is the int() function to reference the field with the date time stamp (=int(d2), then copy/paste special/value back in the original field remove the time.
###### (\$B2>\$C2) - means
metyogi 18th Aug
Hi Simon,
To elaborate the same we need to understand the numerical equivalant of TRUE & FALSE.
In Excel TRUE represents Number 1, & FALSE represents number 0.
Thus if the difference between 2 timings are negative (\$B2>\$C2) gives result as TRUE, i.e. 1 and gets added in the negative value, as this negative value is always less than 1, the entire result turns to a positive value.
This means 8:00 AM - 10:00 PM gives value as -0.583333333 (in general format / Julian number format). This turns to (-0.583333333 + 1 = 0.416666667) means 10:00 Hrs.

Regards,
Yogi
1 Vote
###### MROUND
aevans@... 20th Aug
Two functions shown I was not aware - time calculations often cause issue, so tip on how to handle crossing over midnight also useful.
Contributr
###### Great!
ssharkins@... 22nd Aug
The article was really about rounding, but I'd glad I pulled this old example sheet out of my grab-bag -- it's just a bit of icing that so many are finding the elapsed time formula useful! Thanks for letting me know!
1 Vote
###### Making it harder than necessary
lmarks@... 20th Aug
Susan,

Why did you insert an extra column (F) and these two steps
1.Enter the specific interval, in minutes, in F2. In this case, enter 15 to represent the quarter-hour interval.
2.In G2, enter the following formula
=TIME(HOUR(E2),MROUND(MINUTE(E2),\$F\$2),0)

When you could have simply put the value 15 into the formula like this?
=TIME(HOUR(E2),MROUND(MINUTE(E2),15),0)
Contributr
###### Dynamic
ssharkins@... 22nd Aug
Entering the time interval into the sheet and referring to that cell allows for more flexibility in the process. When someone wants to change the interval, you won't have to change the formula, just enter the new interval. You might think it won't change and you might think changing the one formula is no big deal, but that's because the example is simple. When applying these rounding formulas and functions to real apps, things usually get more difficult and less dependable. As sure as someone says "That'll never change!" -- it'll change.
1 Vote
###### For some Timekeeping you need to round the input.
Ray Baker 22nd Aug
For some companies you need to round the input values first. For example my company would take the in/out times of 8:08a and 3:06p and round them to 8:15 and 3:00 first and credit you 6:45 hours. Whereas the formulas above would calculate (6:58) a rounded value of 7:00 hours.
Thanks for the tips! I learned new functions to work with time.
1 Vote
###### (\$C2-\$B2 + (\$B2>\$C2))
Many thanks for an excellent article and for the excellent replies to my query. i especially thank Yogi for the tutorial on beating the "24 hour " clock calculation.
Keyboard Shortcuts:
Prev
Next
Toggle
###### Join the conversation
Formatting +
BB Codes - Note: HTML is not supported in forums
• [b] Bold [/b]
• [i] Italic [/i]
• [u] Underline [/u]
• [s] Strikethrough [/s]
• [q] "Quote" [/q]
• [ol][*] 1. Ordered List [/ol]
• [ul][*] · Unordered List [/ul]
• [pre] Preformat [/pre]
• [quote] "Blockquote" [/quote]

Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion.