Posts: 7,371
Threads: 181
Joined: Jul 2020
Reputation:
27
I need to do some working out of times and wages for some staff members. Doing it the old-fashioned way is a ball ache. However, its complicated.
For instance, if someone works 21:30 until 07:30 next day, the period between 00:00 and 06:00 would be paid at Time + 1/3. However, if this shift starts on a Saturday, and goes into Sunday, it would be Time + 1/3 and Time +1/2 for Sunday.
It's a ballache to work out on paper. Is there anyway that days and times can be input into Excel and formulas and functions could work out what hours are normal pay, Time+1/3 etc?
Posts: 3,316
Threads: 58
Joined: Sep 2022
(05-05-2023, 01:06 PM)MrFizz Wrote: I need to do some working out of times and wages for some staff members. Doing it the old-fashioned way is a ball ache. However, its complicated.
For instance, if someone works 21:30 until 07:30 next day, the period between 00:00 and 06:00 would be paid at Time + 1/3. However, if this shift starts on a Saturday, and goes into Sunday, it would be Time + 1/3 and Time +1/2 for Sunday.
It's a ballache to work out on paper. Is there anyway that days and times can be input into Excel and formulas and functions could work out what hours are normal pay, Time+1/3 etc?
Format the from and to cells to be dd/mm/yyyy hh:mm. So you can find hours since midnight and up to to 6am. Use Weekday() to return the day of the week.
Posts: 4,108
Threads: 50
Joined: Feb 2019
Reputation:
13
You'd just have to use a combination of IF statements based on the data set. So if saturday do this, if sunday do this etc
Posts: 13,407
Threads: 402
Joined: Jul 2019
Reputation:
14
this is where managing data should be done in a database and not a spreadsheet.
it's doable, but you're looking at writing a decent chunk of code to get all the logic down.
Posts: 3,316
Threads: 58
Joined: Sep 2022
(05-05-2023, 02:45 PM)AnelkasBeard Wrote: You'd just have to use a combination of IF statements based on the data set. So if saturday do this, if sunday do this etc
Yes you're using Weekday() to check day of the end date and also calculating the number of minutes the end date is after midnight up til six to calc the uplift (if any). Add to that the number of minutes from the start date to midnight and any minutes in the end date after six am and you're there.