IF functions with Time range

Copper Contributor

Hi All,

 

Apologies if this has been posted already, but I couldn't find on the previous answers precisely this.

I am creating a file that calculates Overtime of our staff. As per our local legislations, if a worker overtime falls AFTER 4PM and BEFORE 4AM, his overtime is multiplied by 1.5. If it falls AFTER 4AM and BEFORE 4PM, by 1.25.

 

I tried a simple =IF(A1>16:00,1.5,1.25), but it did not work. Should I write 16:00 on another format?

Then I typed 16:00 on a single isolated cell (for example B2) and did =IF(A1>B2,1.5,1.25). This worked but only until midnight. All overtimes past midnight are incorrectly showing as 1.25.

 

Can you please assist?

2 Replies

Hi Thomaz,

 

To compare the time you shall use its number representation, e.g. 16:00 is equal to 16/24. If you use time only (without the dates) that could be like

=IF((A1>4/24)*(A1<16/24),1.25,1.5)

 

Hi Sergei.

That was brilliant! It worked perfectly.

 

Thank you very very much!!

 

:D