Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time format
I received an excel spreadsheet where data for time was entered in two
columns with a decimal point instead of a colon (e.g the time of twelve thirty four was entered as 12.34 instead of 12:34). Is there any way I can insert a formula to convert the data to the correct format so that I can calculate the time elapsed correctly? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time format
=--(SUBSTITUTE(E1,".",":"))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Meb" wrote in message ... I received an excel spreadsheet where data for time was entered in two columns with a decimal point instead of a colon (e.g the time of twelve thirty four was entered as 12.34 instead of 12:34). Is there any way I can insert a formula to convert the data to the correct format so that I can calculate the time elapsed correctly? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time format
Do a Find and Replace (CTRL-H) replacing all . with :
-- Kind regards, Niek Otten Microsoft MVP - Excel "Meb" wrote in message ... |I received an excel spreadsheet where data for time was entered in two | columns with a decimal point instead of a colon (e.g the time of twelve | thirty four was entered as 12.34 instead of 12:34). Is there any way I can | insert a formula to convert the data to the correct format so that I can | calculate the time elapsed correctly? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time format
Thank you for the very prompt and perfect solution. You just saved me 3 hours
work! "Bob Phillips" wrote: =--(SUBSTITUTE(E1,".",":")) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Meb" wrote in message ... I received an excel spreadsheet where data for time was entered in two columns with a decimal point instead of a colon (e.g the time of twelve thirty four was entered as 12.34 instead of 12:34). Is there any way I can insert a formula to convert the data to the correct format so that I can calculate the time elapsed correctly? Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time format
I followed your advice but got a number of consistent errors with the zeros.
If the original time was stated as 11.00 the revised time was stated to be 00:00. If the original time was stated to be 14.10 the revised time was stated to be 14:01 not 14:10. However, if the original was stated to 12.18 the revised was correctly stated to be 12:18. Is this a problem with the Excel programming? "Niek Otten" wrote: Do a Find and Replace (CTRL-H) replacing all . with : -- Kind regards, Niek Otten Microsoft MVP - Excel "Meb" wrote in message ... |I received an excel spreadsheet where data for time was entered in two | columns with a decimal point instead of a colon (e.g the time of twelve | thirty four was entered as 12.34 instead of 12:34). Is there any way I can | insert a formula to convert the data to the correct format so that I can | calculate the time elapsed correctly? Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time format
Your problem is that the number stored in the cell isn't necessarily what
you think it is from what is displayed. 11.00 will be stored as 11 14.10 will be stored as 14.1 You'll see better what's going on if you format as General. To use your Find and Replace you may first need to change to a consistent pattern by using =TEXT(A2,"00.00"). You might as well then do the replace in the same formula, so =SUBSTITUTE(TEXT(A2,"00.00"),".",":") would give you text, or =--SUBSTITUTE(TEXT(A2,"00.00"),".",":") would give you a time if you format the cell appropriately. -- David Biddulph "Meb" wrote in message ... I followed your advice but got a number of consistent errors with the zeros. If the original time was stated as 11.00 the revised time was stated to be 00:00. If the original time was stated to be 14.10 the revised time was stated to be 14:01 not 14:10. However, if the original was stated to 12.18 the revised was correctly stated to be 12:18. Is this a problem with the Excel programming? "Niek Otten" wrote: Do a Find and Replace (CTRL-H) replacing all . with : -- Kind regards, Niek Otten Microsoft MVP - Excel "Meb" wrote in message ... |I received an excel spreadsheet where data for time was entered in two | columns with a decimal point instead of a colon (e.g the time of twelve | thirty four was entered as 12.34 instead of 12:34). Is there any way I can | insert a formula to convert the data to the correct format so that I can | calculate the time elapsed correctly? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting a time format to a String format in Excel | Excel Discussion (Misc queries) | |||
Custom Cell format to mimic time format | Excel Discussion (Misc queries) | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions | |||
How to format cells in Excel for time in format mm:ss.00 | Excel Worksheet Functions | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions |