0

I have come across a problem I don't understand where it's coming from.

I want to calculate the total amount of money that an employee is paid according to Time In, Break In, Break Out, Time Out, and the Rate per hour of course.

E20 is Rate per hour
F20 is Time in
G20 is Break in
H20 is Break out
I20 is Time out
J20 is Total Hours worked
L20 is Total amount of money

I have this on J20 to calculate the hours

=SUM(IF(G20<F20,G20+1,G20)-F20,If(I20<H20,I20+1,I20)-H20)

I have this on L20 to calculate the money

=IF(I20="","£0.00",J20*E20*24)   

{I wanted to make sure the employee imputs Time Out before calculation takes place}

Everything works perfectly until the employee actually doesn't have a break. Then he will only input times on "Time in" and "Time Out", and the break sections will be blank... J20 will still count the hours correctly; but L20 will calculate a stupid amount of money and I don't even know what is it calculating and where is it taking the numbers from?

migrated from superuser.com Nov 1 '17 at 11:25

This question came from our site for computer enthusiasts and power users.

1

Please try in J20:

=I20-F20+(I20<F20)-H20+G20-(H20<G20)

In adjusting for overnight you have added 1s that are 'counted' even when no break.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy