Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting date/time serial values to cumulative time totals...
I have 2 years worth of cumulative phone usage time totals in an Excel
workbook that are submitted by our phone service provider. The phone usage columns are cumulative minutes and seconds by day (mm:ss) and some have been entered as string formulas: ="1655:33" However, many time totals have been converted to a date/time serial number, so I have a displayed value of 939:53:00, that is actually 2/8/1900 3:53:00 AM. What I need is the string equivalent of the displayed value and not the date/time serial number. Ive tried a number of VB conversion UDFs and I can only successfully convert the values that do not exceed 23:59. At the moment Im stumped. Any suggestions would be greatly appreciated. Thanks ahead of time... Kevin Backmann |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting date/time serial values to cumulative time totals...
Use a custom number format, enclosing minutes in square brackets, like this:
[m]:ss "Kevin B" wrote: I have 2 years worth of cumulative phone usage time totals in an Excel workbook that are submitted by our phone service provider. The phone usage columns are cumulative minutes and seconds by day (mm:ss) and some have been entered as string formulas: ="1655:33" However, many time totals have been converted to a date/time serial number, so I have a displayed value of 939:53:00, that is actually 2/8/1900 3:53:00 AM. What I need is the string equivalent of the displayed value and not the date/time serial number. Ive tried a number of VB conversion UDFs and I can only successfully convert the values that do not exceed 23:59. At the moment Im stumped. Any suggestions would be greatly appreciated. Thanks ahead of time... Kevin Backmann |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting date/time serial values to cumulative time totals..
Thanks, but unfortunately what is really minutes and seconds is treated as
hours:minutes:seconds and displayed as 939:53:00, instead of 939:53. What I'm looking for is way to convert my displayed value to a string, identical to the displayed value, I can lop off the :00 with left()/len() function combo if 2 colons are found in the string. -- Kevin Backmann "hmm" wrote: Use a custom number format, enclosing minutes in square brackets, like this: [m]:ss "Kevin B" wrote: I have 2 years worth of cumulative phone usage time totals in an Excel workbook that are submitted by our phone service provider. The phone usage columns are cumulative minutes and seconds by day (mm:ss) and some have been entered as string formulas: ="1655:33" However, many time totals have been converted to a date/time serial number, so I have a displayed value of 939:53:00, that is actually 2/8/1900 3:53:00 AM. What I need is the string equivalent of the displayed value and not the date/time serial number. Ive tried a number of VB conversion UDFs and I can only successfully convert the values that do not exceed 23:59. At the moment Im stumped. Any suggestions would be greatly appreciated. Thanks ahead of time... Kevin Backmann |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting date/time serial values to cumulative time totals...
Remember that if you enter the data as 1655:33 it will be treated as hh:mm,
not as mm:ss If you type it in as 0:1655:33, or as 1655:33.0, then it will be treated as mm:ss, and (as hmm said) it will be displayed that way if the cell is formattted as [m]:ss If you do have data already stored as hh:mm instead of mm:ss, then you can divide by 60 (either with a formula or by copying a cell with 60 in it and then using Edit/ Paste Special/ Divide on your data cells), to convert 1655:33:0 to 0:1655:33. -- David Biddulph "hmm" wrote in message ... Use a custom number format, enclosing minutes in square brackets, like this: [m]:ss "Kevin B" wrote: I have 2 years worth of cumulative phone usage time totals in an Excel workbook that are submitted by our phone service provider. The phone usage columns are cumulative minutes and seconds by day (mm:ss) and some have been entered as string formulas: ="1655:33" However, many time totals have been converted to a date/time serial number, so I have a displayed value of 939:53:00, that is actually 2/8/1900 3:53:00 AM. What I need is the string equivalent of the displayed value and not the date/time serial number. I've tried a number of VB conversion UDF's and I can only successfully convert the values that do not exceed 23:59. At the moment I'm stumped. Any suggestions would be greatly appreciated. Thanks ahead of time... Kevin Backmann |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting date/time serial values to cumulative time totals...
While not the most elegant of solutions, I've saved the Excel file as a tab
delimited file and imported the file into Access. I think that I will solve my short term issue, but if anyone has any suggestions on the serial date to string conversion I will gladly accept any pointers. I've Googled the issue and there is tons 'o' stuff on calculating time differences, but that's not what I'm looking for here. Thanks to hmm (Mr. Hours & Minutes?) and to all the contributers on this site. -- Kevin Backmann "Kevin B" wrote: I have 2 years worth of cumulative phone usage time totals in an Excel workbook that are submitted by our phone service provider. The phone usage columns are cumulative minutes and seconds by day (mm:ss) and some have been entered as string formulas: ="1655:33" However, many time totals have been converted to a date/time serial number, so I have a displayed value of 939:53:00, that is actually 2/8/1900 3:53:00 AM. What I need is the string equivalent of the displayed value and not the date/time serial number. Ive tried a number of VB conversion UDFs and I can only successfully convert the values that do not exceed 23:59. At the moment Im stumped. Any suggestions would be greatly appreciated. Thanks ahead of time... Kevin Backmann |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting UTC timestamp value to date and time | Excel Discussion (Misc queries) | |||
How Do I Eliminate the Serial Number for time from Date? | Excel Discussion (Misc queries) | |||
converting time values | Excel Discussion (Misc queries) | |||
Format serial date and time | Excel Discussion (Misc queries) | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions |