I am trying to add times H1:H21 in H23. Any ideas?
I am trying to add times H1:H21 in H23. Any ideas?
they are not time values - they are text
you need to change to time values to get a number or at least a decimal part of a number - which is the way time is worked out
then you can sum
also format the sum cell to [H]:MM which will display more than 24
see attached i have added some columns to show
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.
Can you concert the actual H column? I can't.
you maybe able to using VBA , I had to create the new column using =timevalue(H1)
i dont know VBA well enough
OR
create a column with timevalue(H1) and copy down
now copy that range
Paste Special - text only into H to get valid time values
Select the cells H1:H21 then click on Data | Text-to-columns, then click Finish on the firs panel.
Hope this helps.
Pete
So far so good. I wrote the macro to do the conversion and it worked. My next question is that I use this =SUMPRODUCT((((ISNUMBER(SEARCH("40",T2:T5000))+ISNUMBER(SEARCH("41",T2:T5000))+ISNUMBER(SEARCH("43",T2:T5000))+ISNUMBER(SEARCH("47",T2:T5000))+ISNUMBER(SEARCH("81",T2:T5000))+ISNUMBER(SEARCH("83",T2:T5000)))*(G2:G5000={"PM","PDM"})))*(P2:P5000="CLOSE")) for finding the number of workorders that are closed. How do I sum up the hours of closed workorders in column AG2:AG5000 that fit this formula?
Where does your existing formula appear? (which cell, which sheet?)
Where do you want the new formula to go?
If the existing formula counts the number of orders, then this:
=SUMPRODUCT((((ISNUMBER(SEARCH("40",T2:T5000))+ISNUMBER(SEARCH("41",T2:T5000))+ISNUMBER(SEARCH("43",T2:T5000))+ISNUMBER(SEARCH("47",T2:T5000))+ISNUMBER(SEARCH("81",T2:T5000))+ISNUMBER(SEARCH("83",T2:T5000)))*(G2:G5000={"PM","PDM"})))*(P2:P5000="CLOSE"),AG2:AG5000)
(changes in red) should add up the times from column AG where those records occur. You might need to apply a custom format of [h]:mm:ss so that the sum does not wrap at 24 hours.
Hope this helps.
Pete
The formula was in AX6 (List of Workorders) sheet. I want to new one to go in AZ6 (same sheet). I would expect a result of approx. 1917 hrs.
I was wrong on my result set. Still trying to get the correct number.
The correct result set should be about 864 hrs.
Got it. Thank you everyone.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks