Results 1 to 6 of 6

Thread: Excel Payroll Formulas - help!?

  1. #1

    Unhappy Excel Payroll Formulas - help!?

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	excel help.jpg 
Views:	21 
Size:	63.9 KB 
ID:	2487   Click image for larger version. 

Name:	excel 2.jpg 
Views:	26 
Size:	73.9 KB 
ID:	2488  

  2. #2
    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,

  3. #3
    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! Click image for larger version. 

Name:	EXCEL 3.jpg 
Views:	9 
Size:	52.4 KB 
ID:	2489

  4. #4
    Sure, just wrap it in an IF statement:

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

    Best of luck,

  5. #5
    You are an absolute STAR! Thank you!

  6. #6
    You're very welcome

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts