Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time from text to numeric
I have a column of time logs ranging from:
1:00:00:00 to 0:01 I need to split this into days, hours, minutes, and seconds. They are not the same length so LEFT or RIGHT functions wont work, and I am not savvy enough in VB to program the split. Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time from text to numeric
If they are *text* as your subject line says, then use Data/ Text to
Columns/ Delimited, and specify the colon as delimiter. -- David Biddulph "Eric Wixom" wrote in message ... I have a column of time logs ranging from: 1:00:00:00 to 0:01 I need to split this into days, hours, minutes, and seconds. They are not the same length so LEFT or RIGHT functions wont work, and I am not savvy enough in VB to program the split. Any suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time from text to numeric
I didn't explain it will, sorry. the values are coming from a counter, it
starts at 0:01 and then when it hits 23:59:59 it will roll over to 1:00:00:00 Excel is not recognixing it as time, so I can't use TIMEVALUE or similar functions to break it down. Excel is recognizing it as text. I need to be able to get Excel to recognize the Day, Hour, Minute, and Second. I know in programing you would use a split function, does Excel have something similar? "David Biddulph" wrote: If they are *text* as your subject line says, then use Data/ Text to Columns/ Delimited, and specify the colon as delimiter. -- David Biddulph "Eric Wixom" wrote in message ... I have a column of time logs ranging from: 1:00:00:00 to 0:01 I need to split this into days, hours, minutes, and seconds. They are not the same length so LEFT or RIGHT functions wont work, and I am not savvy enough in VB to program the split. Any suggestions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time from text to numeric
You can do this in two steps. One, force your data to be consistently
d:hh:mm:ss, using: =RIGHT("0:00:00:"&IF(OR(LEN(A1)=4,LEN(A1)=7),"0"," ")&A1,10) Second, convert to days plus time: =LEFT(A2,1)+TIMEVALUE(RIGHT(A2,8)) This will work for up to 10 days. You can display it using a format like: d hh:mm:ss Is this good enough? Regards, Fred. "Eric Wixom" wrote in message ... I didn't explain it will, sorry. the values are coming from a counter, it starts at 0:01 and then when it hits 23:59:59 it will roll over to 1:00:00:00 Excel is not recognixing it as time, so I can't use TIMEVALUE or similar functions to break it down. Excel is recognizing it as text. I need to be able to get Excel to recognize the Day, Hour, Minute, and Second. I know in programing you would use a split function, does Excel have something similar? "David Biddulph" wrote: If they are *text* as your subject line says, then use Data/ Text to Columns/ Delimited, and specify the colon as delimiter. -- David Biddulph "Eric Wixom" wrote in message ... I have a column of time logs ranging from: 1:00:00:00 to 0:01 I need to split this into days, hours, minutes, and seconds. They are not the same length so LEFT or RIGHT functions wont work, and I am not savvy enough in VB to program the split. Any suggestions? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting time from text to numeric
Fantastic! Thank you!
I had to add LEN=5 and LEN=8 to the formula to make it come out right for all the different times, but it works great. The TIMEVALUE is not working correctly though, I don't know why yet. I will play with it but you won half the battle for me. Thanks again, Eric "Fred Smith" wrote: You can do this in two steps. One, force your data to be consistently d:hh:mm:ss, using: =RIGHT("0:00:00:"&IF(OR(LEN(A1)=4,LEN(A1)=7),"0"," ")&A1,10) Second, convert to days plus time: =LEFT(A2,1)+TIMEVALUE(RIGHT(A2,8)) This will work for up to 10 days. You can display it using a format like: d hh:mm:ss Is this good enough? Regards, Fred. "Eric Wixom" wrote in message ... I didn't explain it will, sorry. the values are coming from a counter, it starts at 0:01 and then when it hits 23:59:59 it will roll over to 1:00:00:00 Excel is not recognixing it as time, so I can't use TIMEVALUE or similar functions to break it down. Excel is recognizing it as text. I need to be able to get Excel to recognize the Day, Hour, Minute, and Second. I know in programing you would use a split function, does Excel have something similar? "David Biddulph" wrote: If they are *text* as your subject line says, then use Data/ Text to Columns/ Delimited, and specify the colon as delimiter. -- David Biddulph "Eric Wixom" wrote in message ... I have a column of time logs ranging from: 1:00:00:00 to 0:01 I need to split this into days, hours, minutes, and seconds. They are not the same length so LEFT or RIGHT functions wont work, and I am not savvy enough in VB to program the split. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display leading zeros in numeric value without converting to text | Excel Discussion (Misc queries) | |||
Converting Numeric values to Text | Excel Worksheet Functions | |||
converting text to numeric data | Excel Worksheet Functions | |||
Converting variable text strings to numeric | Excel Discussion (Misc queries) | |||
Converting Text into a Numeric Value and Totalling | Excel Worksheet Functions |