![]() |
|
FAQ Off Excel Experts - Printable Version +- WBAUnofficial (https://wbaunofficial.org.uk) +-- Forum: WBAUnofficial (https://wbaunofficial.org.uk/forumdisplay.php?fid=3) +--- Forum: WBAUnofficial (https://wbaunofficial.org.uk/forumdisplay.php?fid=4) +--- Thread: FAQ Off Excel Experts (/showthread.php?tid=29879) |
FAQ Off Excel Experts - MrFizz - 05-05-2023 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? RE: FAQ Off Excel Experts - Calgary_Baggie - 05-05-2023 (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. 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. RE: FAQ Off Excel Experts - AnelkasBeard - 05-05-2023 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 RE: FAQ Off Excel Experts - MassDebater - 05-05-2023 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. RE: FAQ Off Excel Experts - Fido - 05-05-2023 (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! RE: FAQ Off Excel Experts - AnelkasBeard - 05-05-2023 (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 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. RE: FAQ Off Excel Experts - MrFizz - 05-05-2023 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
RE: FAQ Off Excel Experts - Calgary_Baggie - 05-05-2023 (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. |