calculating multiple payment types

**dugus** · 07-11-2010, 10:03 AM

Basically I need to calculate total hours worked for a day.

Start and finish for lunch and start and finish for dinner. The problem is that after midnight the pay changes so I need to seperate the pre and post midnight hours.

I inserted this formula into F4 and F5
=IF(D4>24, (D4-24),0)

___a____b____c____d____e____f
1
2
3 __am_______pm_____pre24__post24
4 __12__16___18___22___10___-2
5 __11__14___18___26___9_____2

If there are hours after midnight(24) it works otherwise it subtracts the hours.

What am I doing wrong? tried without inside brackets as well.

The hours go past 24 because a single working day goes 'til 3am calculating total hours won't work unless 1am=25, 2am=26, etc.

Thank you for your help

**pb71** · 07-11-2010, 01:09 PM

Here's one idea, and there is no need to substitute 1am for 25 etc.

Please see attached.

**arthurbr** · 07-11-2010, 02:29 PM

Hi and welcome to the board

if your data layout does not match pb71 's example please attach a sample workbook.
Does the pay change starting at midnight, or is ti different if the shift goes from one day to the next?

**pb71** · 07-11-2010, 04:11 PM

The example workbook attached is based on the example given in the original post, so I would hope it bears some resemblance to the actual layout

. To calculate pay, the results in columns E and F will probably be wanted in number format. Therefore, using the example in the attached workbook and with columns A to D formatted as hh:mm, use the following:

Format cell E3 as a number and use:
=24*((B3-A3)+IF(D3*24<=3,1-C3,D3-C3))

drag down

Format cell F3 as a number and use:
=24*(IF(D3*24>3,0,D3))

drag down

**dugus** · 07-12-2010, 03:48 AM

Sorry guys but still not working quite the way it's meant to.

I forgot to mention that you can either work a day or night or double. When hours are input for AM and PM or PM only it works perfectly but if you only work an AM the results get messed up.

Anymore help would be greatly appreciated. I've tried to work with it but can't get it quite right. USing the example attached in previous post as it's exactly the layout I need.

Thanks again

**pb71** · 07-12-2010, 12:42 PM

In the example workbook, with E3 formatted as a number, use this in E3 instead:

=24*(IF(OR(A3="",B3=""),0,B3-A3)+IF(OR(C3="",D3=""),0,IF(24*C3<=3,0,IF(24*D3<=3,1-C3,D3-C3))))

and drag down

and with F3 formatted as a number, use this in F3 instead:

=24*(IF(OR(C3="",D3=""),0,IF(24*D3>3,0,IF(24*C3<=3,D3-C3,D3))))

and drag down

EDIT:
Assumes that the PM shift ends at 3am and that the PM shift could start from midnight. Change the times in the formula to suit your shift times.