How to calculate Regular working hours and Over time

[rehanemis]

Hi,

I am facing issue to get the result of Regular hours and over time based on the following conditions.

Input is as:
A1 = Start Time
B1= End time

Conditions as:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Regular hours[/TD]
[TD]Over Time (1x)[/TD]
[TD]Over Time (1.5 x)[/TD]
[TD]Over Time(sunday- 1.5x)[/TD]
[TD]Over Time Sunday(2x)[/TD]
[/TR]
[TR]
[TD]8:00 AM to 5: PM[/TD]
[TD]6:00 AM to 7:59 AM[/TD]
[TD]9:00 PM to 5:59 AM[/TD]
[TD]6:00 AM to 9:00 PM[/TD]
[TD]9:01 PM to 5:59 AM [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5:00 PM to 9:00 PM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would like to get the formula that is for each column (as mentioned above). Suppose a person worked from 8:00 AM to 9:00 PM then its regular hours will be in regular hours column and over time in 1x and so on.

There may be a chance a person worked from 8:00 AM to 7:59 AM for 24 hours so each column will contains related hours.

any suggestion ?

 

[DRSteele]

I suggest you ensure that when a person works from X to Y his accompanying data will have the date as well as the time. In other words, all this type of data should be an Excel Date and Time Serial Number. Otherwise, clock maths gets cumbersome, complex and frustrating.

So, could you please provide a couple of dozen rows of varied sample work times and dates for each workman?

 

[rehanemis]

So the data rows should like these:

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="class: xl63, width: 82"]Wednesday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]8/1/2018[/TD]
[TD]8:00 AM[/TD]
[TD]9:00 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]8/2/2018[/TD]
[TD]2:00 PM[/TD]
[TD]6:00 AM[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

There are multiple condition for a single record so I think there may be very long formula using if , and , or etc).

I suggest you ensure that when a person works from X to Y his accompanying data will have the date as well as the time. In other words, all this type of data should be an Excel Date and Time Serial Number. Otherwise, clock maths gets cumbersome, complex and frustrating.

So, could you please provide a couple of dozen rows of varied sample work times and dates for each workman?