Excel Payroll Formulas - help!?

Hi everyone, I'm going out of my mind and I'm in need of help. I'm new here and apparently I can't attach pictures/links or even my bloody email address so please try the best you can without the picture.

I started a new job and it's my responsibility to set up a spreadsheet to show each employees shift for the day, as well as start and finish times for shifts. I currently have it set up using the MOD formula.

I need the yellow cell (J7) to not only calculate the shift time but to bump it up to 8 hours if it equals less than 8 hours. I also need to then deduct 45 minutes for a break (breaks are on every shift, I'm not sure if the break is taken off the 8 hours or whether the minimum shift needs to be 8h45m so then the 45m can be deducted) If you have ideas for both options that would be amazing, but any help is greatly appreciated. I'm happy to add new cells etc, as long as it works

I'm absolutely stuck, someone on my previous question gave me this formula: J7 =IF(IF(H7>I7,(I7+1),I7)-H7-TIME(0,45,0)<TIME(8,0,0),TIME(8,0,0),(IF(H7>I7,(I7+1),I7)-H7-TIME(0,45,0))) which gave: 33608:00 which isn't what I wanted.

H7 being the end shift time
I7 being the start shift time
J7 being where I want the total to be

If anyone has any ideas whatsoever I'd be truly grateful! Thank you in advance!

Good afternoon,

If I understand, then you need something like this:

= Max ( Mod ( I7 - H7 , 1 ) , 8 / 24 ) - ( 0.75 / 24 )

If the minimum shift is 8:45 (or 8.75 hours), just change the 8 above to 8.75. This just says to take the higher of either the calculation or 8 hours and then deduct breaks.

Hope this helps,

Thank you! It worked! Only thing is that when the start and end times for the shift are 0:00, the total is 7:15. Is there anyway for that to be 0:00? Sorry to be a pain, I'm a bit of a newbie!

Sure, just wrap it in an IF statement:

= IF ( I7 - H7 = 0 , 0 , ** FORMULA ** )

Best of luck,

You are an absolute STAR! Thank you!

You're very welcome :smile: