Reply With Quote
Originally Posted by dcope7
12 what? sseconds? minutes? hours? days?
12 what? sseconds? minutes? hours? days?
When you key in 2:05 you're typing in 2:05 AM
When you key in 10:05 you're typing 10:05 AM
The actual values in those cells are 0.086805556 and 0.420138889. These are fractions of a day; 0.25 would be 6am, 0.75 would be 6pm. The format you choose only changes what's displayed, not the value itself.
Excel's date and time works in days.
If you add 12, you're adding 12 days, or 288 hours. The value in D16 is 13 (days) showing as 312 hours.
It could be all. most of the time it will be only 30 minutes if there is any.
Cell D16!!
If I take out the formula in rows 11 and rows 25 and change the format to h:mm:ss everything works fine but I have to manually calculate all the times except rows15,16,29 and 30 then cell s 30 works fine. I put the formulas in so me nor my staff would have to manually calculate all the times, just the pick up,arrival, departure and return. We are currently having to total the times manually. If the times were all the same like, 10:00 / 3:00 it would not be a problem. I was just trying ot make it easier on everyone involved. I can change it to different formats but then I get military times.
It subtracts any non-billable times
I'm talking about row 16, NOT ROW 12!
The 12 is a hard-coded 12 in the formula, and it adds (not subtracts) 12. The formula in cell D16 (yes, the one in row 16, not row 12).
What's the value 12 doing there?
I now have no hair left to tear out.
Ok row 16 calculates rows 11 and 15, plus it subtracts row 12 and yes I know I have it adding but if you actually add 0:30 in row 12 it will subtract from the totals in row 16. I am attaching the original sheet I started with and you can see how it worked before I added formulas to rows 11 and 25 and formatted with am/pm.
It does not! it adds the value 12 and doesn't even look at row 12! In this context it adds 12 days (=288 hours). Barmy.
Until you get formulae right in the cells you want to sum, you've no hope of getting the correct total hours.
I'll look at your newly attached file later - out of time at the moment.
Posting Permissions
Bookmarks