FAQ Off Excel Experts
#1
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?
Reply
#2
(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.
Reply
#3
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
Reply
#4
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.
Reply
#5
(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

Surely you could get that from a formula based on the date I.e. if it’s 05/05/2023 it’ll know it’s a Friday anyway? 

This is from someone who knows VLOOKUP, CONCAT and fugg all else!
Reply
#6
(05-05-2023, 03:28 PM)Fido Wrote:
(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

Surely you could get that from a formula based on the date I.e. if it’s 05/05/2023 it’ll know it’s a Friday anyway? 

This is from someone who knows VLOOKUP, CONCAT and fugg all else!

Yeah just use WEEKDAY, which gives you the day of the week from the date. But then you have to tell excel what to do next. It's certainly doable, slightly finicky because it uses time.

Also, start using XLOOKUP instead of VLOOKUP, much easier to do.
Reply
#7
Some useful ideas there. Will have a play around and see what i get. I'll be a fuggin hero if this job gets automated  Big Grin
Reply
#8
(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.
Reply


Forum Jump:


Users browsing this thread: