Timecard Question

[ColtsRocker1]

I am trying to take the elapsed time and subtract it by an integer.. How would I go about doing that?

Example: C5-D5-E5 = F5

C5 = 8:00 AM (time in)
D5 = 5:00 PM (time out)
E5 = 1 (hour for lunch)
F5 = 8 (total hours worked)

Timecard.JPG

[6StringJazzer]

First, you want to subtract D5-C5, not C5-D5. Your formula gives a negative number, and Excel will not display negative date/time (that's why you are getting #############).

Excel time is in units of days, so if you want to subtract an hour you either need to convert to a time value, or use 1/24.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[modytrane]

=(C6-B6)*24-D6

Try This:

[modytrane]

Sorry about the extra equation. pls ignore =(C6-B6)*24-D6

[modytrane]

Post #2 is correct. you need to use something like:

=(D5-C5)*24-E5

[ColtsRocker1]

Ok, 10-4 on the subtraction being reversed. That at least helps with the #### error.

So, now I have 2 more issues (yay).

C6 = 8 PM
D6 = 4:15 PM
E6 = .25 (15 minute lunch)
F6 = 8:15 (it should read 8)

2nd issue, why isn't the sum working at the bottom?

Timecard.JPG

[6StringJazzer]

The problem with a picture is I can't see what formula you are using. If you post any more data, please post your actual file. We can't update a picture, and not many people want to type in your data and formulas from scratch. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

I'm sorry, my formula was an illustration of the concept and wasn't correct for plugging into your sheet. You should really use

Please Login or Register  to view this content.

(the TIME function will not work for you, sorry)

When adding time values, Excel will add up the result to a date/time value, which as I mentioned before is in units of days. So if your sum is more than 24 hours, and you just display the time, you just get the time portion. For example, in your data, the sum is 40 hours and 15 minutes, which is 1 day + 16 hours + 15 minutes. You just see 16:15. To see this number as a number of hours, use a Custom format for the cell of

[h]:mm

and you will see 40:15.

(The other two posts also had valid formulas but they result in the number of hours rather than a time value. It all depends on what you want to do.)

[ColtsRocker1]

That worked.. Thank you