| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
number to time format when digit length varies
I am struggling to convert following "number" format cells as 24 hrs "time"
format (hh:mm) 516 2306 637 202 2353 300 3 6 It should then appear as: 05:16 23:06 06:37 02:02 23:53 03:00 00:03 00:06 Any help will be a great relief. Thanks/regards, |
|
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
|
number to time format when digit length varies
You can't use format to do this, you would need a formula (or a macro) and
help cells, then you can copy and paste as special over the old values, with your value in A1 =(INT(A1/100)+MOD(A1,100)/60)/24 format as hh:mm -- Regards, Peo Sjoblom (No private emails please) "UM Win" wrote in message ... I am struggling to convert following "number" format cells as 24 hrs "time" format (hh:mm) 516 2306 637 202 2353 300 3 6 It should then appear as: 05:16 23:06 06:37 02:02 23:53 03:00 00:03 00:06 Any help will be a great relief. Thanks/regards, |
|
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
|
number to time format when digit length varies
It worked. Your kind help saved my day(s).
Thank you so much ! Regards, UM Win -- "Peo Sjoblom" wrote in message ... You can't use format to do this, you would need a formula (or a macro) and help cells, then you can copy and paste as special over the old values, with your value in A1 =(INT(A1/100)+MOD(A1,100)/60)/24 format as hh:mm -- Regards, Peo Sjoblom (No private emails please) "UM Win" wrote in message ... I am struggling to convert following "number" format cells as 24 hrs "time" format (hh:mm) 516 2306 637 202 2353 300 3 6 It should then appear as: 05:16 23:06 06:37 02:02 23:53 03:00 00:03 00:06 Any help will be a great relief. Thanks/regards, |
|
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
|
number to time format when digit length varies
This does the same thing, but may be a bit more intuitive:
=TIME(INT(A1/100),MOD(A1,100),0) In microsoft.public.excel Peo Sjoblom wrote: You can't use format to do this, you would need a formula (or a macro) and help cells, then you can copy and paste as special over the old values, with your value in A1 =(INT(A1/100)+MOD(A1,100)/60)/24 format as hh:mm -- Gary L. Smith Columbus, Ohio |
|
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
|
number to time format when digit length varies
One mo
=--(TEXT(A1,"00\:00")&":00") And format as hh:mm UM Win wrote: I am struggling to convert following "number" format cells as 24 hrs "time" format (hh:mm) 516 2306 637 202 2353 300 3 6 It should then appear as: 05:16 23:06 06:37 02:02 23:53 03:00 00:03 00:06 Any help will be a great relief. Thanks/regards, -- Dave Peterson |
|
#6
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
|
number to time format when digit length varies
Maybe but it will format AM and PM automatically while my formula will give
the option to the regional settings and if the time is more than 24 hours like 2530 your formula will only return 01:30 even if it is formatted as [hh]:mm -- Regards, Peo Sjoblom (No private emails please) "Gary Smith" wrote in message ... This does the same thing, but may be a bit more intuitive: =TIME(INT(A1/100),MOD(A1,100),0) In microsoft.public.excel Peo Sjoblom wrote: You can't use format to do this, you would need a formula (or a macro) and help cells, then you can copy and paste as special over the old values, with your value in A1 =(INT(A1/100)+MOD(A1,100)/60)/24 format as hh:mm -- Gary L. Smith Columbus, Ohio |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Custom Format on 16 Digit Number | Excel Discussion (Misc queries) | |||
| Length of time from date/time custom format | Excel Discussion (Misc queries) | |||
| how to format cell in excel for 12 digit number starting with a 0 | Excel Discussion (Misc queries) | |||
| Find character when the length varies | Excel Discussion (Misc queries) | |||
| How do i format a 5 to 6 digit number into the correct date? | Excel Worksheet Functions | |||
Linear Mode
