Need help with total monthly hours
- Thread starter dcope7
- Start date
Thank you for your response but, I have tried that. The total should be 103:12:00 hrs for the month. I have formulas in cells D11:S11and D25:R31 to total worked hrs for the day and D15:S15, D29:r29 to total travel times. Not sure that has anything to do with getting the total hrs for the month.
- Joined
- Dec 16, 2012
- Messages
- 2,319
- Reaction score
- 40
- Points
- 48
- Excel Version(s)
- 365
Temporarily format all the cells included in your sum formula [h]:mm:ss and visually chack that they contain the values you expect. Especially cells D16 and H16 among others.
Last edited:
No, that gives military time. I think I've tried every formula there is. The highlighted cell will give correct totals when I don't have the formula in the D11 and D30 cells but I need that to get the daily hours.
- Joined
- Dec 16, 2012
- Messages
- 2,319
- Reaction score
- 40
- Points
- 48
- Excel Version(s)
- 365
Nonsense! It gives the number of hours in each cell.
If there is no times in the cells then there are no hours worked. I have all cells needed formatted. Weekends there is no work or days absent but those change every month and I don't want to have to keep re-formatting the sheet every month.
I key in the pick up times and arrival times, I have the billable rows calculate the hours worked. I key in the departure and return times, travel time calculate and row 16 calculates rows 11 and 15
- Joined
- Dec 16, 2012
- Messages
- 2,319
- Reaction score
- 40
- Points
- 48
- Excel Version(s)
- 365
- Joined
- Dec 16, 2012
- Messages
- 2,319
- Reaction score
- 40
- Points
- 48
- Excel Version(s)
- 365
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.
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.
- Joined
- Dec 16, 2012
- Messages
- 2,319
- Reaction score
- 40
- Points
- 48
- Excel Version(s)
- 365
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.
- Joined
- Dec 16, 2012
- Messages
- 2,319
- Reaction score
- 40
- Points
- 48
- Excel Version(s)
- 365
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.
- Joined
- Dec 16, 2012
- Messages
- 2,319
- Reaction score
- 40
- Points
- 48
- Excel Version(s)
- 365
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.