Forum Discussion
Solved
IF formula on time duration
Hello Everyone,
I have a column with duration in hh:mm:ss which I need to evaluate on the bases of certain time gaps. I want to add Greater than n hours or less than n hours as text in the adjacent cell. Here is data example -
Formula I am using is -
=IF(B2<TIME(2,0,0),"Less than 2 hours",IF(B2<TIME(4,0,0),"Less Than 4 Hours",IF(B2<TIME(8,0,0),"Less than 8 hours",IF(B2<TIME(16,0,0),"Less than 16 hours",IF(B2<TIME(32,0,0),"Less than 32 hours","Greater than 32 Hours")))))
The formula is not working if the time is more than 16 hours. After 16 hours, it by default add "Greater than 32 hours" as you can see in the screenshot above.
Can someone please help me figure out what is that I am doing wrong?
Any help is appreciated.
The TIME function returns clock time, so TIME(32,0,0) is equivalent to TIME(8,0,0).
Use 32/24 instead of TIME(32,0,0)
(Excel stores times as numbers with 1 day = 24 hours as unit. 32 hours = 32/24 day)
4 Replies
The TIME function returns clock time, so TIME(32,0,0) is equivalent to TIME(8,0,0).
Use 32/24 instead of TIME(32,0,0)
(Excel stores times as numbers with 1 day = 24 hours as unit. 32 hours = 32/24 day)
- Do you mean I should update formula , like this ?
....IF(B2<TIME(32/24,0,0),"Less than 32 hours","Greater than 32 Hours")))))
I tried but it is not changing the outcome 😞No. exactly what I wrote:
...IF(B2<32/24,"Less than 32 hours","Greater than 32 Hours")))))
