Hi guys
I do always try to solve these things myself, but as usual, excel is not doing what it's supposed to.
Just trying to sum several amounts of time. It is more than 24hrs in total, so I changed the format to [h]:mm, but it's come up with some wierd number
Am attaching the spreadsheet
Any help glady appreciated
Thanks
Total work time.xlsx
i get 9069:43
in the status bar for the sum
and also for
=SUM(B1:B33)
what are you getting and what are you expecting ?
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
That's rather a rash statement! Excel will be doing what you have asked it to do, but whether what you are asking is correct remains to be seen. I'll have a look.... but as usual, excel is not doing what it's supposed to.
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.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I can see what the problem is immediately. Your TIME references are actually DATES and TIMES. You need to remove the DATE element.
Or try this:
=SUM(B1:B33)-SUM(INT(B1:B33))
AliGW on MS365 Beta Channel (Windows 11) 64 bit
SUM
Sheet: Sheet1
Is that the result you're expecting?
Column B is DATE and Time:
in C1
Formula:
Please Login or Register to view this content.
Copy down
SUM is 333:43:00
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Yes that's what I get. But just off the top of my head there are 33 entries, averaging around 10hrs. So should be in the regio of 300 - 400hrs, not 9069
Did you read post #4???
Last edited by AliGW; 03-28-2024 at 06:14 AM. Reason: Workbook added.
Thanks guys
I can see that solved it, but I don't understand why all my values include a date, and how to get rid of it. Don't really want to be fixing it with a formula.
I don't understand because I copied and pasted the times from another spreadsheet, then went to Format cells > Time. Which shows up as hours : mins : ss. I never had a date in there before, but now it's showing each value as 11/01/1900.
Even if I change it to custom > hh:mm, I still have the date
Is there not an option for a simpleton like me, to just have the time and nothing else?
Thanks
You know what guys, I'm happy with that. Even if I can't convert the original values to just a time with a couple of clicks, I used John Topley's solution, and copied the data into a new column which removed the date. Then I copied that and pasted "values" in a new column. Then summed those values and it worked. Bit long winded, but now I have the pure data. Just was hoping to do it with the original data.
I'll mark as solved.
Thanks again
Glad to have helped.
If you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks