• 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.

Formula Sum

krishnaraj

krishnaraj

New Member
Dear Chandoo


I have a excel timesheet which has the cells in the format of Custom h:mm. I have 5 working days and if I sum these 5 cells the sum cell is not providing the correct total. Assuming I have 8:00 in 3 cells, for some reason the total shows as 0:00. It has something to do with the formatting, but in my case I cannot change the formatting. Please help.


Thanks in advance.


Krishnaraj
 
Sorry, forgot to mention further investigation results. Basically it cancels out after every 24 hour period, how to over come this problem?
 
Hello,


You need to tweak the cell formatting to [h]:mm


This will allow the hours to be aggregated beyond 1 day.
 
Thanks Colin, It was very helpful. Now I have another issue, I am subtracting values from 2 cells using the same format. It holds good for a positive value and does not recoganize the negative value.


Example: If cell A1 has a value of 10:00 and Cell A2 has a value of 8:00. Cell A3 has a formula of A1-A2 and it gives the correct answer which is 2:00, if the value exceeds 10:00 on A2 I get a result of ##########. Can you please provide a solution for this?
 
Negative dates and times are displayed as pound signs in Excel as explained here:

http://support.microsoft.com/kb/182247


There are two workarounds described on that link so pick whichever best suits your situation.
 
You must log in or register to reply here.
Back
Top