Can't fix cell Time format

[rotateclockwise]

I am going nuts getting this simple data sheet to work.

My problem might be the imported time data and I don't know how to fix it in excel.

This is partly what I am going for but as you can see the numbers result doesn't turn out right:

StartTime EndTime Hours Rate Result
9:45 AM 6:00 PM 8:15 $13.00 $4.47
6:00 PM 11:00 PM 5:00 $9.00 $1.88

When I click on one of the time cells this how the time looks in the formula window 9:45:00 AM

what cell formats work for time going to dollars?? What else can I do? trying to figure it out myself is killing my day here.

 

[HalfAce]

Hello,
Assuming your "Hours" column are the results of formula(s), like =(B2-A2) to get the actual number of hours difference between B2 and A2, your cell (C2) should be formatted as Number (2 decimal places), and your formula in C2 should look like this:=(B2-A2)*24 (Result in your example is 8.25)

Then your Result column formula is simply Rate times Hours.
=D2*C2 - (end result is $13.00 times 8.25 Hrs. = $107.25)

Am I guessing right on this?

 

[rotateclockwise]

Yes. BTW The formula for hours is: =IF(H3>=F3,H3-F3,F3-H3) if I add the *24 to it it throws the hours off. The result column is as you stated.

Ideas?

Hello,
Assuming your "Hours" column are the results of formula(s), like =(B2-A2) to get the actual number of hours difference between B2 and A2, your cell (C2) should be formatted as Number (2 decimal places), and your formula in C2 should look like this:=(B2-A2)*24 (Result in your example is 8.25)

Then your Result column formula is simply Rate times Hours.
=D2*C2

Am I guessing right on this?

 

[HalfAce]

I was assuming your data was starting in column A and goint to column E.
If this is correct, then what's in columns F & H?

If that's not correct, then what columns are your data (from your example) in?

 

[barry houdini]

Why this?

=IF(H3>=F3,H3-F3,F3-H3)

If this is to handle shifts which work overnight then it won't work

Try

=(H3-F3+(F3>H3))*24

format as number

 

[rotateclockwise]

You are correct. I think you hit the nail on the head with just that one tid bit about column C being numerical format. here's the result:

StartTime EndTime Hours Rate Result
9:45 AM 6:00 PM 8.25 9.00 $74.25

I changed column c to numerical format and then added the *24 to the formula in column c and bingo!

I was assuming your data was starting in column A and goint to column E.
If this is correct, then what's in columns F & H?

If that's not correct, then what columns are your data (from your example) in?

 

[rotateclockwise]

Ok, I'll try it..

Why this?

=IF(H3>=F3,H3-F3,F3-H3)

If this is to handle shifts which work overnight then it won't work

Try

=(H3-F3+(F3>H3))*24

format as number

 

[rotateclockwise]

It worked, of course! thanks, man!

Ok, I'll try it..

Why this?

=IF(H3>=F3,H3-F3,F3-H3)

If this is to handle shifts which work overnight then it won't work

Try

=(H3-F3+(F3>H3))*24

format as number

 

[HalfAce]

If you're looking to calculate times that go beyond midnight as Barry mentioned, you can change your formula in C2 to this:

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