Confused with Formula and IF function - Need to Pull out report at 3 PM today :(

Dear All,

See attached file.

I have Column F "Extra Hours". Now looking at this column i want some status description in Column H

Here are the conditions:

If Extra Hours is < 00:00 -- SHORT FALL
If Extra Hours is between 05:00 and 10:00 -- > 5 Hr.
If Extra Hours is between 10:00 and 20:00 -- > 10 Hr.
If Extra Hours is greater than 20:00 -- > 20 Hr.

Since column F in in HH:MM format i have tried to multiply it by 24 and tried to do the same with column G but no success.

Please advise.
Regards
Mohammad

what about between 0 and 5 hr

Zaid in your second uploaded file calculation is already done what else you want

Raman,

Looks OK with 5, 10 & 20 Hr. logic but SHORT FALL logic is wrong. I want SHORT FALL wherever the Extra Hours is less than 00:00 ( For Ex. see row 13 , 155:45-180:00 is - 24:15 ).

Please advise.

Regards
Mohammad

Zaid if time is between 0 and 5 hour what you want

Hemesh,

"Less than 5"

Regards
Mohammad

Zaid, make your table like this

Hrs Status
-50 Short Fall
0 less Than 5 Hrs
5 5 Hrs
10 10 Hrs
20 20 Hrs

If answer satisfy your query Click " * "

Hemesh,

I am still confused, why you have mentioned -50 , Short Fall is for anything less than 0.

Regards
Zaid

Zaid vlook searching the value in hr column if it is not finding the exact match it will look for the next lower value in the column and if is not finding that value than it will return error. So if valur is less than 0 in that case it will look for next lower value and that will be - 50


If answer resolves your query click star and mark threa as solved

Everything working perfect but it is not showing anything for negative values. You can try also.

One more error. For all the values in 20s it is showing "Less than 5 Hr". Don't know why ?