Discussion on:
View:
Show:
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.)
It's a simple trick that allows one simple formula to handle time values that don't fall within the same 24-hour day.
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.
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.
Another excellent article. I have learned a lot from your posts and really appreciate them.
Rupert
Rupert
Can anyone give me any other examples of when you'd use these functions other than timekeeping? Thanks.
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!
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.
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.
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
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
Two functions shown I was not aware - time calculations often cause issue, so tip on how to handle crossing over midnight also useful.
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!
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)
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)
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.
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.
Thanks for the tips! I learned new functions to work with time.
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
