Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anaother time format question
I get text files from a phone switch. I then have a macro that does some
calculations and saves it as 2003 format .xls. I am using 2007. The times come across like: 8:48, which is actually 8:48:00 AM. However, some come across as :53, which stores as :53. I need it to be in the format of 12:53:00 AM, for calculations. If I manually put a 0 in front, 0:53, it stores fine, but I do not want to manually do this on hundreds of files, hundreds of entries every month. The concatenate into a different cell, saves it as text, so no calculations there. Is there VB formula that will change :53 to 12:53:00 AM which can be run on the whole file, changing only those that are less than a minute? If it can do it in the same cell, all the better, but if not, that's ok, too. Thanks, Ron |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anaother time format question
Assuming A2 contains your "time" entry, put this in another cell...
=--("0"&A2) and format that cell as Time (picking whichever time format you want). -- Rick (MVP - Excel) "Ron" wrote in message ... I get text files from a phone switch. I then have a macro that does some calculations and saves it as 2003 format .xls. I am using 2007. The times come across like: 8:48, which is actually 8:48:00 AM. However, some come across as :53, which stores as :53. I need it to be in the format of 12:53:00 AM, for calculations. If I manually put a 0 in front, 0:53, it stores fine, but I do not want to manually do this on hundreds of files, hundreds of entries every month. The concatenate into a different cell, saves it as text, so no calculations there. Is there VB formula that will change :53 to 12:53:00 AM which can be run on the whole file, changing only those that are less than a minute? If it can do it in the same cell, all the better, but if not, that's ok, too. Thanks, Ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anaother time format question
Assuming A2 contains your "time" entry, put this in another cell...
=--("0"&A2) and format that cell as Time (picking whichever time format you want). -- Rick (MVP - Excel) "Ron" wrote in message ... I get text files from a phone switch. I then have a macro that does some calculations and saves it as 2003 format .xls. I am using 2007. The times come across like: 8:48, which is actually 8:48:00 AM. However, some come across as :53, which stores as :53. I need it to be in the format of 12:53:00 AM, for calculations. If I manually put a 0 in front, 0:53, it stores fine, but I do not want to manually do this on hundreds of files, hundreds of entries every month. The concatenate into a different cell, saves it as text, so no calculations there. Is there VB formula that will change :53 to 12:53:00 AM which can be run on the whole file, changing only those that are less than a minute? If it can do it in the same cell, all the better, but if not, that's ok, too. Thanks, Ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anaother time format question
Ron
Try the below macro. Select the range of cells with time. and run... Sub Macro() Dim cell As Range For Each cell In Selection If Left(cell.Value, 1) = ":" Then cell.Value = "00:" & Mid(cell.Value, 2) End If Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "Ron" wrote: I get text files from a phone switch. I then have a macro that does some calculations and saves it as 2003 format .xls. I am using 2007. The times come across like: 8:48, which is actually 8:48:00 AM. However, some come across as :53, which stores as :53. I need it to be in the format of 12:53:00 AM, for calculations. If I manually put a 0 in front, 0:53, it stores fine, but I do not want to manually do this on hundreds of files, hundreds of entries every month. The concatenate into a different cell, saves it as text, so no calculations there. Is there VB formula that will change :53 to 12:53:00 AM which can be run on the whole file, changing only those that are less than a minute? If it can do it in the same cell, all the better, but if not, that's ok, too. Thanks, Ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anaother time format question
Jacob,
That worked like a charm. The time is stored perfectly. I used your solution as it kept the time in the same cell. Thanks to Rick also. Ron "Jacob Skaria" wrote: Ron Try the below macro. Select the range of cells with time. and run... Sub Macro() Dim cell As Range For Each cell In Selection If Left(cell.Value, 1) = ":" Then cell.Value = "00:" & Mid(cell.Value, 2) End If Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "Ron" wrote: I get text files from a phone switch. I then have a macro that does some calculations and saves it as 2003 format .xls. I am using 2007. The times come across like: 8:48, which is actually 8:48:00 AM. However, some come across as :53, which stores as :53. I need it to be in the format of 12:53:00 AM, for calculations. If I manually put a 0 in front, 0:53, it stores fine, but I do not want to manually do this on hundreds of files, hundreds of entries every month. The concatenate into a different cell, saves it as text, so no calculations there. Is there VB formula that will change :53 to 12:53:00 AM which can be run on the whole file, changing only those that are less than a minute? If it can do it in the same cell, all the better, but if not, that's ok, too. Thanks, Ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Anaother time format question
Jacob,
That worked like a charm. The time is stored perfectly. I used your solution as it kept the time in the same cell. Thanks to Rick also. Ron "Jacob Skaria" wrote: Ron Try the below macro. Select the range of cells with time. and run... Sub Macro() Dim cell As Range For Each cell In Selection If Left(cell.Value, 1) = ":" Then cell.Value = "00:" & Mid(cell.Value, 2) End If Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "Ron" wrote: I get text files from a phone switch. I then have a macro that does some calculations and saves it as 2003 format .xls. I am using 2007. The times come across like: 8:48, which is actually 8:48:00 AM. However, some come across as :53, which stores as :53. I need it to be in the format of 12:53:00 AM, for calculations. If I manually put a 0 in front, 0:53, it stores fine, but I do not want to manually do this on hundreds of files, hundreds of entries every month. The concatenate into a different cell, saves it as text, so no calculations there. Is there VB formula that will change :53 to 12:53:00 AM which can be run on the whole file, changing only those that are less than a minute? If it can do it in the same cell, all the better, but if not, that's ok, too. Thanks, Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Format Question | Excel Worksheet Functions | |||
Date time format question please | Excel Discussion (Misc queries) | |||
One more question pay calcs using [h]:mm format for time worke | Excel Discussion (Misc queries) | |||
Time Format Input question | Excel Programming | |||
Time Format Question | Excel Worksheet Functions |