formula recognising time

July 21, 2010 at 19:09:30
Specs: Windows XP
How do I get a formula to recognise time eg. a payroll.
Start: 06:00
Finish: 09:00

before 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!

See More: formula recognising time

Report •

July 22, 2010 at 06:53:21

Try this:
Format cells A1 and B1 as hh:mm
Enter 06:00 in A1
Enter 09:00 in B1

Format 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:
In cell B3 enter this formula:

This is what I got:

	A	B
1	06:00	09:00
2	19.00	19.00
3	19.25	38.50

B2 shows the total pay before 07:00
and B3 shows the total pay after 07:00


Report •

July 22, 2010 at 17:34:09
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?

Report •

July 22, 2010 at 20:50:35

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:
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.


Report •

Related Solutions

July 25, 2010 at 16:33:38
Its ok, I have figured it out =) thanks anyway!

Report •

Ask Question