Jan 16 2023 04:32 AM
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.
Jan 16 2023 04:43 AM
SolutionThe 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)
Jan 16 2023 07:22 AM
Jan 16 2023 07:35 AM
No. exactly what I wrote:
...IF(B2<32/24,"Less than 32 hours","Greater than 32 Hours")))))
Jan 16 2023 04:43 AM
SolutionThe 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)