 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.

Any help is appreciated.

4 Replies
best response confirmed by Ashar2200 (Occasional Contributor)
Solution

# Re: IF formula on time duration

The TIME function returns clock time, so TIME(32,0,0) is equivalent to TIME(8,0,0).

(Excel stores times as numbers with 1 day = 24 hours as unit. 32 hours = 32/24 day)

# Re: IF formula on time duration

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

# Re: IF formula on time duration

No. exactly what I wrote:

...IF(B2<32/24,"Less than 32 hours","Greater than 32 Hours")))))

# Re: IF formula on time duration

Thank you so much, it worked