| Home |
| Search |
| Today's Posts |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extracting h:mm:ss from text string
Glad I could help.
Micki wrote: Thanks very much Glenn. People like you make this site so great! "Glenn" wrote: =IF(COUNT(FIND(" ",A1))... Check for a space in the data in cell A1 ....,LEFT(A1,FIND(" ",A1)-1)... If there is a space, capture the left portion of the data up to, but not including (the -1 part), the space. This is the number of full days in the data. ....+MID(A1,FIND(" ",A1)+1,LEN(A1))... Add the value found after the space (the +1 part), which Excel interprets as a time value. Excel stores time as a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.). A value of 1 would equal a day. So, the last two steps added the number of full days (451) and the fraction of a day (22:07:34). The number format displays it as requested. ....,--A1) If there is no space found, just use the data as is. The double unary minus (--) forces Excel to convert text entries to their numeric equivalent. Micki wrote: Glenn, when I found 37:30:55 under formatting and used that, it worked. I manually used [h]:mm:ss prior to your post. Now, I thank you and David so much. If you have time and feel like it, I'd appreciate a step by step of what exactly the formula does (like the "-1" and "--1") so I can use it in the future for similar scenarios. I can usually scan others' requests for assistance and find what I need, but after 2 hours of piecing formulas together, I had to give up and ask. "Glenn" wrote: Format / Cells / Number and make sure you select the format [h]:mm:ss;@ for the result cell. On my version of Excel (2003), it shows 37:30:55 in the "Type:" box when you select Time in the "Category:" list. Micki wrote: atm sorry to be a nudge Glenn, but it's returning 22:07:34 and not multiplying the 451 days x 24 to get hours, and then adding the result to 22:07:34 to get total HH:MM:SS. The result should be 10,846 hrs, 7 minutes and 34 seconds.. I'm thinking it may be because HH:MM:SS only recognizes 24 hrs in a day? "Glenn" wrote: Actually that was wrong: =IF(COUNT(FIND(" ",A1)),LEFT(A1,FIND(" ",A1)-1)+ MID(A1,FIND(" ",A1)+1,LEN(A1)),--A1) Glenn wrote: =IF(COUNT(FIND(" ",A1)),LEFT(A1,FIND(" ",A1)-1)+ (RIGHT(A1,SEARCH(":",A1)+1)),--A1) Micki wrote: Sorry for not asking this sooner. The person i was trying to assist just came back to me this morning with the all-in result she was looking for. Where the data in the cell contains the # of days an issue is outstanding, she wants to convert that to hours and add it to the hh:mm:ss. So where the data = 451 22:07:34 she wants to multiply 451 x 24 hrs, and add it to 22 hrs, 7 mins and 27 seconds. Please note that not all data contains # days outstanding. Some only contain 3:39:10 (hh:mm:ss) I am really stumped.) "Micki" wrote: I have raw data that downloads in 2 different formats. 451 22:07:34 (451 = # days a problem is not resolved) 3:39:10 (hrs, minutes, seconds) I need a formula that will enter only the hrs, mins and seconds in an adjacent column. The formula =RIGHT(A33,SEARCH(":",A33)+2) gives me the results I need where the data in column A = 451 22:07:34, but I believe I also need to add an if statement to give me only the hrs, mins, sec for the case where the value is 3:39:10. I have tried using =if(len(a27)8,RIGHT(A33,SEARCH(":",A33)+2),A27, but I just get #Value return. Help is greatly appreciated. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extracting text separated by | from string? | Excel Discussion (Misc queries) | |||
| Extracting text from a string | Excel Worksheet Functions | |||
| Extracting text from string | Excel Worksheet Functions | |||
| Extracting integers from a text string. | Excel Worksheet Functions | |||
| Extracting from a text string | Excel Worksheet Functions | |||
Threaded Mode
