Hello all,
So I have a field that is formatted as text, and shows up like this:"6 day(s) 13 hrs 12 min". I am trying to get an average for these cells, however since it is formatted as such, that isn't possible. Thus I want to convert this to something manageable, perhaps along the lines "dd/hh/mm." What is an easy way to do that?
Thank you all!
-Leo
you an example of your exel file, without confidentional information.
Please also add the desired (expected) result.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
sure, if we can convert it to hours/minutes it would be just as good.
so this is what i have: "6 day(s) 13 hrs 12 min". what I would like is:"157:12" Formatted as: "hh/mm". Let me know if this helps.
Thank you.
I don't want to rebuilt your file, please post it.
=LEFT(A1, FIND(" ", A1)) + SUBSTITUTE(SUBSTITUTE(REPLACE(A1, 1, FIND(" ", A1, 4), ""), "hrs ", ":"), " min", "") and format as [h]:mm
Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
Perfect! Thank you so much.
could you maybe take me through your methodology if it wont take too long. Again, thank you, this is fantastic.
Last question to follow up: The formula you provided works great, however there is one issue:
When there is a date such as: 131 day(s) 21 hrs 48 min, the formula shows up as:#VALUE!
When i change the "4" from within the formula to "5" it works fine, however i would have to manually go through 10,000 fields. Is there any easier way?
the formula: =LEFT(D6460, FIND(" ", D6460)) + SUBSTITUTE(SUBSTITUTE(REPLACE(D6460, 1, FIND(" ", D6460, 4), ""), "hrs ", ":"), " min", "")
Why would you need to do that? Change it to 5 throughout.When i change the "4" from within the formula to "5" it works fine, however i would have to manually go through 10,000 fields.
Just did and it works. thank you. I guess the issue is i don't know what the 5 represents...
What does Help for the FIND function tell you?
I took the time to examine the formula by starting in the middle and working out. This is what I came up with.
Find 9 This finds a space after starting the count 4 characters into A1, the space is the 9th character in A1
Replace 13 hrs 12 min This replaces the above space with nothing
Substitute 13 :12 min This replaces the hrs with a colon
Substitute 13 :12 This replaces the "space min" with nothing
FIND 2
Left 6 First the Find is executed and finds the space at character 2 that leaves 6 as the result
If formatted as dd h:mm the result would have been 06 13:12
However changing the formatting to [h]:mm results in 157:12 ...that is 157 hours and 12 minutes.
That is about as reasonable an explanation of the formula as I can give.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Thanks all, really appreciate it. You guy's make work so much easier.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks