Time Calculation by different rate

[AaruJaan]

Hello Forum,
I would like to see how many hours they work
weekdays from 06.00 to 18.00 (normalyl rate)
weekdays from 18.00 to 24:00 (Evening rate
weekdays from 24..00 to 06.00 (Night rate)
Saturday from 14.00 to 24.00 (Saturday rate)
Sunday from 06.00 to 24.00 (Sunday rate)
And i need to see it for all employees, like Anna work on that time, Betina work on that time.

[zbor]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[AaruJaan]

I have sent Excel file

[zbor]

Where do you calculate the rates?

[AaruJaan]

bottom of sheet

[AaruJaan]

can you help me?

[zbor]

I've look at it and seems need UDF.

If you don't get solution by tomorrow I'll look at it again.

[AaruJaan]

still problem is not solved

[JohnTopley]

Can shifts go over two rates e.g. 22:00 to 06:00?

[AaruJaan]

I would like to see how many hours they work
weekdays from 06.00 to 18.00 (normalyl rate)
weekdays from 18.00 to 24:00 (Evening rate
weekdays from 24..00 to 06.00 (Night rate)
Saturday from 14.00 to 24.00 (Saturday rate)
Sunday from 06.00 to 24.00 (Sunday rate)

[JohnTopley]

You did not answer my question.

Can shifts go over two rates e.g. 22:00 to 06:00?

[AaruJaan]

Sir,I have clear mentioned in summary.

weekdays Mon to Fri 06.00 to 18.00 (10 per hour )
weekdays Mon to Fri 06.00 to 18.00 (12 per hour )
weekdays from 24..00 to 06.00 (15 per hour)
Saturday from 14.00 to 24.00 (20 per hour)
Sunday from 06.00 to 24.00 (25 per hour)

[zbor]

Here, try this UDF.

First copy code to your desired workbook.
Save workbook as .xlsm (not .xlsx)
Then use it as any other function:

Please Login or Register  to view this content.

where:
First range ($A$4:$A$34) are dates
Second ranges (B$4:C$34) are TWO columns times
Third range is string "Normal", "Evening", "Night", "Saturday", "Sunday" that define shifts

After that you can multiply it with each rate as you like:

Please Login or Register  to view this content.