• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Box H9 doesn't work

M

midge

New Member
Hello,

I wonder if you can help? I have used Excel just to do basic accounts. This time, I'm trying to set up a sheet for people to log the times they start and stop work, I want them to be able to input the start and end times of their mornings and afternoon shifts so that if people are working slightly longer or shorter times, it's easy to see how much time they've built up so they can take time off later. I've done the sheet, am start, am finish, pm start, pm finish, input formulas to add each day up and all is okay. the problem comes when I add the days together. Up to Wednesday it works but then comes up with a totally wrong figure from Thursday. Thursday always falls in box H9. I've tried doing this from the beginning on three different computers now, all with the same result. I've triple checked the cell formatting (hh:mm) but I really don't know where to start. It must be something simple that I'm doing or not doing and it's driving me potty. I'd really appreciate any advice you can give but please could you write in simple language as all this is not my strong point.

Many thanks

Midge
 
Whenever your total of hh:mm goes above 24 hours, it will not show 25:00 but will show 1:00, for example.


Also, even though it shows 1:00, value under hood is still equal to 25:00, hence if you want to do something with this value it will still behave as 25:00.


it has nothing to with H9, but just that your Wednesday's total will cross 24hr mark.
 
Thank you so much for that reply, it now makes perfect sense and means I'm not going mad. Do you know which format or other thing I can do in order to to get the result I need?

Thanks again,

Midge
 
In the custom cell formatting, instead of "hh:mm", enter "dd hh:mm"


So, now it will show "01 04:00" instead of "04:00".
 
Midge


I'd suggest you add a small "d" like d"d" h:mm

to the format, just so people aren't confused about Days, Hrs, Mins etc
 
You must log in or register to reply here.
Back
Top