SOLVED

IF formula on time duration

Occasional Contributor

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  - 

 

Ashar2200_0-1673872147877.png

 

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. 

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

@Ashar2200 

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

@Ashar2200 

No. exactly what I wrote:

 

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

Thank you so much, it worked