Can't fix cell Time format

rotateclockwise

New Member
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.

Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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?

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?

HalfAce said:
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?

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?

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

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!

HalfAce said:
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?

Ok, I'll try it..

barry houdini said:
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

It worked, of course! thanks, man!

rotateclockwise said:
Ok, I'll try it..

barry houdini said:
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

If you're looking to calculate times that go beyond midnight as Barry mentioned, you can change your formula in C2 to this:
Code:
``=(B2+(B2<A2)-A2)*24``

Replies
5
Views
595
Replies
4
Views
1K
Replies
16
Views
945
Replies
18
Views
838
Replies
15
Views
640

1,217,318
Messages
6,135,857
Members
449,965
Latest member
Ckl43

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back