| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert Number to Time
I am struggling with some data I exported from an Oracle Aspect DB into
Excel. One column is a list of times in the format: HHMMSS but if there is no value for hours or minutes, there will be no leading zeros. This means the value can have anywhere from 1 to 6 digits. For example, here are some sample times in this column: ORIG_TIME 1 46 121 404 3008 12342 175632 These would translate into the following times: 12:00:01 AM 12:00:46 AM 12:01:21 AM 12:04:04 AM 12:30:08 AM 01:23:42 AM 05:56:32 PM I would like a formula/method for converting these numbers into time values as recognized by Excel. I would be fine with the times in AM/PM or 24 Hour time. Any assistance would be greatly appreciated. Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert Number to Time
One way:
Assume number values are in A1 on down. Enter this formula in B1 and copy down as needed: =TIME(MID(A1*0.000001,3,2),MID(A1*0.000001,5,2),MI D(A1*0.000001,7,2)) Then format in the TIME style of your choice. I guess the first selection listed would be your best choice. -- Biff Microsoft Excel MVP "BenS" wrote in message ... I am struggling with some data I exported from an Oracle Aspect DB into Excel. One column is a list of times in the format: HHMMSS but if there is no value for hours or minutes, there will be no leading zeros. This means the value can have anywhere from 1 to 6 digits. For example, here are some sample times in this column: ORIG_TIME 1 46 121 404 3008 12342 175632 These would translate into the following times: 12:00:01 AM 12:00:46 AM 12:01:21 AM 12:04:04 AM 12:30:08 AM 01:23:42 AM 05:56:32 PM I would like a formula/method for converting these numbers into time values as recognized by Excel. I would be fine with the times in AM/PM or 24 Hour time. Any assistance would be greatly appreciated. Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert Number to Time
The solution you proposed worked perfectly! Thank you so much for your help
on this, it will really save me a good amount of time. Thanks! "T. Valko" wrote: One way: Assume number values are in A1 on down. Enter this formula in B1 and copy down as needed: =TIME(MID(A1*0.000001,3,2),MID(A1*0.000001,5,2),MI D(A1*0.000001,7,2)) Then format in the TIME style of your choice. I guess the first selection listed would be your best choice. -- Biff Microsoft Excel MVP "BenS" wrote in message ... I am struggling with some data I exported from an Oracle Aspect DB into Excel. One column is a list of times in the format: HHMMSS but if there is no value for hours or minutes, there will be no leading zeros. This means the value can have anywhere from 1 to 6 digits. For example, here are some sample times in this column: ORIG_TIME 1 46 121 404 3008 12342 175632 These would translate into the following times: 12:00:01 AM 12:00:46 AM 12:01:21 AM 12:04:04 AM 12:30:08 AM 01:23:42 AM 05:56:32 PM I would like a formula/method for converting these numbers into time values as recognized by Excel. I would be fine with the times in AM/PM or 24 Hour time. Any assistance would be greatly appreciated. Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert Number to Time
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "BenS" wrote in message ... The solution you proposed worked perfectly! Thank you so much for your help on this, it will really save me a good amount of time. Thanks! "T. Valko" wrote: One way: Assume number values are in A1 on down. Enter this formula in B1 and copy down as needed: =TIME(MID(A1*0.000001,3,2),MID(A1*0.000001,5,2),MI D(A1*0.000001,7,2)) Then format in the TIME style of your choice. I guess the first selection listed would be your best choice. -- Biff Microsoft Excel MVP "BenS" wrote in message ... I am struggling with some data I exported from an Oracle Aspect DB into Excel. One column is a list of times in the format: HHMMSS but if there is no value for hours or minutes, there will be no leading zeros. This means the value can have anywhere from 1 to 6 digits. For example, here are some sample times in this column: ORIG_TIME 1 46 121 404 3008 12342 175632 These would translate into the following times: 12:00:01 AM 12:00:46 AM 12:01:21 AM 12:04:04 AM 12:30:08 AM 01:23:42 AM 05:56:32 PM I would like a formula/method for converting these numbers into time values as recognized by Excel. I would be fine with the times in AM/PM or 24 Hour time. Any assistance would be greatly appreciated. Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert Number to Time
Perhaps someone can help...some months later I've noticed that the formula
below produces errors with some values. These values always seem to end in zero when the error happens, but not all values that end in zero produce the error. Here are some examples of values that produce an error with this formula: 3200 45800 134600 151800 215300 174700 Can anyone suggest a modification or alternative to the formula provided below that can convert HHMMSS numbers to actual times? Thanks! "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "BenS" wrote in message ... The solution you proposed worked perfectly! Thank you so much for your help on this, it will really save me a good amount of time. Thanks! "T. Valko" wrote: One way: Assume number values are in A1 on down. Enter this formula in B1 and copy down as needed: =TIME(MID(A1*0.000001,3,2),MID(A1*0.000001,5,2),MI D(A1*0.000001,7,2)) Then format in the TIME style of your choice. I guess the first selection listed would be your best choice. -- Biff Microsoft Excel MVP "BenS" wrote in message ... I am struggling with some data I exported from an Oracle Aspect DB into Excel. One column is a list of times in the format: HHMMSS but if there is no value for hours or minutes, there will be no leading zeros. This means the value can have anywhere from 1 to 6 digits. For example, here are some sample times in this column: ORIG_TIME 1 46 121 404 3008 12342 175632 These would translate into the following times: 12:00:01 AM 12:00:46 AM 12:01:21 AM 12:04:04 AM 12:30:08 AM 01:23:42 AM 05:56:32 PM I would like a formula/method for converting these numbers into time values as recognized by Excel. I would be fine with the times in AM/PM or 24 Hour time. Any assistance would be greatly appreciated. Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert Number to Time
Is this what those times should be:
3200 = 12:32:00 AM 45800 = 4:58:00 AM 134600 = 1:46:00 PM 151800 = 3:18:00 PM 215300 = 9:53:00 PM 174700 = 5:47:00 PM If those are the expected results try this: =--TEXT(A1,"00\:00\:00") Format as h:mm:ss AM/PM -- Biff Microsoft Excel MVP "BenS" wrote in message ... Perhaps someone can help...some months later I've noticed that the formula below produces errors with some values. These values always seem to end in zero when the error happens, but not all values that end in zero produce the error. Here are some examples of values that produce an error with this formula: 3200 45800 134600 151800 215300 174700 Can anyone suggest a modification or alternative to the formula provided below that can convert HHMMSS numbers to actual times? Thanks! "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "BenS" wrote in message ... The solution you proposed worked perfectly! Thank you so much for your help on this, it will really save me a good amount of time. Thanks! "T. Valko" wrote: One way: Assume number values are in A1 on down. Enter this formula in B1 and copy down as needed: =TIME(MID(A1*0.000001,3,2),MID(A1*0.000001,5,2),MI D(A1*0.000001,7,2)) Then format in the TIME style of your choice. I guess the first selection listed would be your best choice. -- Biff Microsoft Excel MVP "BenS" wrote in message ... I am struggling with some data I exported from an Oracle Aspect DB into Excel. One column is a list of times in the format: HHMMSS but if there is no value for hours or minutes, there will be no leading zeros. This means the value can have anywhere from 1 to 6 digits. For example, here are some sample times in this column: ORIG_TIME 1 46 121 404 3008 12342 175632 These would translate into the following times: 12:00:01 AM 12:00:46 AM 12:01:21 AM 12:04:04 AM 12:30:08 AM 01:23:42 AM 05:56:32 PM I would like a formula/method for converting these numbers into time values as recognized by Excel. I would be fine with the times in AM/PM or 24 Hour time. Any assistance would be greatly appreciated. Thanks! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert Number to Time
Thank you Biff for the additional information and sorry for my delayed
response. Your suggestion does convert the values below into times. But, the result of the formula does not respond to date formatting. Also, the =--TEXT(A1,"00\:00\:00") formula does not return appropriate values when the time value is anything other than one ending in two zeros. Is it possible to achieve my original aim with a single formula that accounts for both scenarios? Many Thanks, Ben "T. Valko" wrote: Is this what those times should be: 3200 = 12:32:00 AM 45800 = 4:58:00 AM 134600 = 1:46:00 PM 151800 = 3:18:00 PM 215300 = 9:53:00 PM 174700 = 5:47:00 PM If those are the expected results try this: =--TEXT(A1,"00\:00\:00") Format as h:mm:ss AM/PM -- Biff Microsoft Excel MVP "BenS" wrote in message ... Perhaps someone can help...some months later I've noticed that the formula below produces errors with some values. These values always seem to end in zero when the error happens, but not all values that end in zero produce the error. Here are some examples of values that produce an error with this formula: 3200 45800 134600 151800 215300 174700 Can anyone suggest a modification or alternative to the formula provided below that can convert HHMMSS numbers to actual times? Thanks! "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "BenS" wrote in message ... The solution you proposed worked perfectly! Thank you so much for your help on this, it will really save me a good amount of time. Thanks! "T. Valko" wrote: One way: Assume number values are in A1 on down. Enter this formula in B1 and copy down as needed: =TIME(MID(A1*0.000001,3,2),MID(A1*0.000001,5,2),MI D(A1*0.000001,7,2)) Then format in the TIME style of your choice. I guess the first selection listed would be your best choice. -- Biff Microsoft Excel MVP "BenS" wrote in message ... I am struggling with some data I exported from an Oracle Aspect DB into Excel. One column is a list of times in the format: HHMMSS but if there is no value for hours or minutes, there will be no leading zeros. This means the value can have anywhere from 1 to 6 digits. For example, here are some sample times in this column: ORIG_TIME 1 46 121 404 3008 12342 175632 These would translate into the following times: 12:00:01 AM 12:00:46 AM 12:01:21 AM 12:04:04 AM 12:30:08 AM 01:23:42 AM 05:56:32 PM I would like a formula/method for converting these numbers into time values as recognized by Excel. I would be fine with the times in AM/PM or 24 Hour time. Any assistance would be greatly appreciated. Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert number to time | Excel Discussion (Misc queries) | |||
| How do I convert run time(45,502h 30m) to number 45,502.5 ?? | Excel Worksheet Functions | |||
| convert number to time | Excel Worksheet Functions | |||
| Convert a number to a time | Excel Discussion (Misc queries) | |||
| convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) | |||
Linear Mode
