Separate the quantity working hours as a Normal,OT1,OT2,Sundays and Holidays as required

Hi,


I need a formula help to calculate a sum of hours such as (Normal Hours, OT1 Hours, OT2 Hours, Sundays, and Holiday Hours) in column W6:Z10 based on data in column C:V.


Sample sheet with expected results are enclosed.


Thanks,

Hi. Good Day.

Please find attached the excel. The values that's arrived out of the formula here is the logic you have asked for. Verify the data manually for first line item. ID 12312.
There are totally only 3 days where it's less than or equal to 10 hours. The total you summed up gives 50?

In the formula - i have shared here gives the values as per the needs you placed in post. Share the Sunday's date List. The same can be adopted.

Please note all the hourly cells are covered in the formula framed.

Let me know, if this is what you are looking for or what needs to be done.

Thank You.

Regards.
Perpectuals

Thanks for the reply,

The hours up to or less than 10 are considered normal working hours on all days except Sundays and holidays; if the hours exceed 10 in a normal day (example: 15 working hours in a normal day 10 are normal working hours, 4 are OT1 hours, 1 is OT2 hours)

OT1 hours are between 10 hours and 14 hours, in a normal working day (14-10=4 or 12-10=2 etc.)

In normal working days, OT2 hours are between 14 and 24 (16-14=2, 18-14=4 etc.)

The total hours of Sundays and holidays are considered as OT2 hours.


Thanks,

A formula solution
In W6 dragged down

Formula:

Please Login or Register  to view this content.

and in Z6

Formula:

Please Login or Register  to view this content.

Many thanks to all of you for your respective solutions.

As such, I am seeking a single cell formula to return results in four columns at once, or a normal formula to return results in each column separately.

I would appreciate it if someone could look into my request.

What is wrong with the two formulae I suggested?

Originally Posted by Fluff13

What is wrong with the two formulae I suggested?

Yes, that's fine, I'm just looking for column z results to also be included in one cell formula or a normal formula that returns the results in each column separately for ease of understanding.

Not sure I understand why, but for W

Formula:

Please Login or Register  to view this content.

for X

Formula:

Please Login or Register  to view this content.

and for Y

Formula:

Please Login or Register  to view this content.

Thank you for addressing this formula solution in the simplest way, and all these formulas work well with shorter data, but with larger data, it would be difficult to separate work days and holidays, Sundays that in thousands of rows.