How do I get a formula to recognise time eg. a payroll.

Start: 06:00

Finish: 09:00before 7am is paid at $ 19 p/h and after 7am is paid at $19.25 p/h.

I want to be able to type in the hours then in a different cell the hours for before 7 automatically goes in it and the cell below the hours after 7 goes in it.

Please help!

Hi, Try this:

Format cells A1 and B1 as hh:mm

Enter 06:00 in A1

Enter 09:00 in B1Format the four cells A2 to B3 as numbers with two decimal places.

Enter the pre-07:00 payrate in A2 (19.00)

Enter the post-07:00 payrate in A3 (19.25)In cell B2 enter this formula:

=24*(TIME(7,0,0)-A1)*A2

In cell B3 enter this formula:

=24*(B1-TIME(7,0,0))*A3This is what I got:

A B 1 06:00 09:00 2 19.00 19.00 3 19.25 38.50B2 shows the total pay before 07:00

and B3 shows the total pay after 07:00Regards

Thankyou! That works =) But I am trying to get the hours automatically transferred to a pay slip eg.

In the calendar you type the hours:

1 2 3

A Mon Tues Wed

B Start 6:00 6:00

C Finish 9:00 10:00

D TOTAL 3.00 4.00

Total hours(D2) =((C2-B2+(C2<B2))*24)

When I go to add the total amount of hours up before and after 7 it will work except for when the person hasn't worked that day and therefore the cell is blank. eg. Wed. I will get a number that is much bigger. Any ideas what I am doing wrong?

Hi, To help sort this out you need to post actual data with correct row and column data.

In your post you have row and column labels reversed.

Also your formula:

=((C2-B2+(C2<B2))*24)

is not right.

C2-B2 is 'OK'

but +(C2<B2) is not

you appear to be adding a comparison.In Excel, "C2<B2" will be evaluated as either TRUE or FALSE

C2 is either less than B2 or it is not.As a result you are either adding True(=1) or FALSE(=0) to the result of C2-B2.

Can you describe what you are trying to achieve with this formula.

Regarding

Any ideas what I am doing wrong?you need to provide very specific details. I can't work out what is wrong from what you have posted.Regards

Its ok, I have figured it out =) thanks anyway!

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History