LinkBack URL
About LinkBacks
Hi All,
In attached file I need help while doing sum where total working hours are not showing correct.
So could you please check.
Sum of hours needed
Hi All,
In attached file I need help while doing sum where total working hours are not showing correct.
So could you please check.
Click on (*), if you agree.
Can you tell us what results you expect? What is the purpose of your + sign?
=+SUM(C2:X2)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Some of your times in the dated columns have a date in front of them (11/01/1900 ) - if you remove this, does it work as expected?
=+ is used to Lotus 123
Please make the Post as solved, when you get your answer & Click * if you like my suggestion
As AliGW suggests, it's the dates, remove the dates and the figures drop drastically.
Keep your data consistent.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
I used find and replace to remove the dates. Now B4 is 220:13:00 and B14 is 202:22:00.
C4:X4 and C14:X14 contains the "date" 1/11/1900 (probably 11 days) in addition to times. Similarly for C8:X8 and C13:X13, which you did not comment.Originally Posted by vjharry
So the SUMs are correct.
But C:X are formatted as only h:mm:ss, so you cannot see that time is more than 24 hours.
Format C:X as Custom [h]:mm:ss to see the correct number of hours.
PS: It might be tempting to use the Custom format d:hh:mm:ss. And that will seem to work. However, it will display only up to 31 days as intended. That is because "d" is really day of the month, not number of days. The integer values 0 through 31 are interpreted as days of Jan 1900.
Ali, the + is probably a hang-over from Lotus or something, and has no effect on the calc.
@ vjharry, try this, copied down...
=SUMPRODUCT(MOD(C2:X2,1))
Some of your rows (as pointed out above) have years in them, this should take care of that
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I don't think the dates are intended at all: each column represents one day in October, so you would not expect more than one day in any one cell, would you?
Thanks for that, I realised it didn't affect the calculation.
I used to use Lotus 123, and thats how you started a formula - took me ages to get out of the habit lol, they all came out =+
The methods suggested for removing the year will all work well, but if that is how the data comes in (maybe from another program?) then removing that each time could become onerous and perhaps might get forgotten - hence my SUMPRODUCT().
If it is a once-off, then by all means use the suggestions to remove the year
Last edited by FDibbins; 05-29-2014 at 12:56 PM.
You might very well be correct. But I do not presume to know what the values represent. For example, they might be the elapsed time of work that end on those dates. That might include days before the end-date.I don't think the dates are intended at all: each column represents one day in October, so you would not expect more than one day in any one cell, would you?
In any case, you and others explained how to remove the integer component (date or number of days), if that is the correct solution.
I explained how to see the "problem" (source of the disparity) in the first place, namely formatting C:X as [h]:mm:ss instead of h:mm:ss.
vjharry, and only vjharray, can decide which responses are useful.
Hi, We can remove "+" from =+SUM(C2:X2)....
Actual formula is =SUM(C2:X2)..... But still answer isnot coming
i tried that also but still not coming...
Actually its exported from an application in which i have sum of all hours ... (there are some days where formatting is not correct)
What have you tried?
tried formula..
Can you confirm whether or not the dates in those cells should be there? If not, have you used Find and Replace to remove them?
I use that formula but in 6th row its not working
268 #VALUE! 10:24:00 10:04:00 8:56:00
yes i confirming that ...
I dont need dates . I just want to see my sum in total hours
Thanks in Advance
You can remove that +
there will not effect there by putting that in formula
Yes, we understand about the +. Have you read ALL of the responses in this thread?
For that row, I get 29:24:00...what were you expecting?
Take a look at the attached - I put the totals on the right so you still have your values. You can move it to B
Thanks a LOT now i am having answer
Glad you got what you needed and thanks for the feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks