Need of overtime formula

[Bomba]

Hello

I need a formula to calculate overtime between two cells E5 and F5 where E5 is the starting time and F5 is the finish time. I need that if time in cells E5 and F5 is >= 07:00 and <=00:00, overtime will be paid at the rate of 1.5 for every hour and at the rate of 2 for every hour if the the time is from 00:00 to 07:00. I tried to create a formula but I can't manage.I don't know if one can write midnight as 00:00.

Thanks in advance

[HansV]

I'm slightly confused. Please explain clearly:
1) When normal rate is paid
2) When 1.5 times normal rate is paid
3) When 2 times normal rate is paid.
You can write midnight as 00:00, the formula will take care of that.

[Bomba]

1. From 07:00 in morning to midnight at 1.5 times normal rate and
2. From midnight to 07:00 in the morning at 2 times normal rate.
Hope this will help you.
Thanks

[HansV]

See the attached demo.
Formula for 1.5x:
=24*MAX(0, MIN(1, F5+(F5<E5))-MAX(TIME(7,0,0), E5))
Formula for 2x:
=24*MOD(F5-E5, 1)-G5

Overtime.xlsx

[SpeakEasy]

>) When normal rate is paid

Yes, this confused me as well. And the answer didn't clarify. ot iomplies there are NO normal working hours. Burt I guess this might be for a special condition - e.g for staff who would not normally work on a particular day.

[HansV]

That's how I interpreted it in the end too.

[Bomba]

Very nice job Master, exactly what I need.
Thanks a lot.

[ChrisGreaves]

... I tried to create a formula but I can't manage.

Hi Bomba.
In situations like this I was taught to use a logical diagram; some call them Logic Tables.

IMG_20250114_092528815.jpg

These are implemented with paper and pencil.
In the diagram above I have drawn a rectangle, and recognizing that you have two relationships as inputs - E5 and F5 - I have split
The first table into a two-by-two rectangle.
The two columns show the values for E5.
The two rows show the rows for F5.

You will notice that I had a slight change of mind while completing the first table; that's why paper and pencil are much faster than typing in (and then deleting) program code of any kind.

The only condition for overtime pay that I could see occurs in that top-left cell.

That suggests that only that one condition effects whether or not Overtime is awarded. but it says nothing about the overtime rate; YET!

The second table specifies the overtime rate according to the midnight to 7 a.m. range of work; that is, whether the rate is to be paid at 2x or at only 1.5x.

You can think of that second table sitting inside the top-left square of the top table.

The pseudocode logic now reads :=
IF E5>= 0 and F5 <=0
    IF E5 = 00:00
        Rate=2
    ELSE
        Rate=1.5
    ENDIF
ELSE
ENDIF

At this point you have probably spotted the same problem as have I; That suggests an error in my thinking or an error in the statement of the problem, or both.
Resolve that error (still with paper and pencil) and re-do the table(s) and you have your IF-statement(s).

You can test these tables with real values, still using pencil and paper, and the testing will be much faster that setting up tests on a computer.

I don't know if one can write midnight as 00:00.

This depends on the stage known as "implementation". I suppose that you are using Excel/VBA, in which case you will need to determine how to represent<midnight> in Windows Excel/VBA.
That's why people like me suggests that you set up a variable, or at least a constant named "Midnight" and set that variable or constant to your local definition of midnight.

Cheers, Chris

[Bomba]

Hi Chris,
Really appriciate your fantastic explanation.
Thanks a lot

[ChrisGreaves]

Really appreciate your fantastic explanation.

[pedantic]Not the stuff of fantasy, but reality[/pedantic]
Experienced VBA programmers, Excel formula mavens and the like, can often enough quote a bit of code (sometimes called a programming idiom) off the top of their head.
Do not be misled into thinking that a good goal is to be able to emit code without thinking.

Many of us use some sort of tool to design a piece of code.

Decision tables, like the example I gave, are just one tool in our toolkit to help us to develop solutions that work the first time, without hours spent patching up a spontaneous piece of code that was not based on a solid foundation of logic.

Play around with decision tables and see how you feel.
Cheers, Chris