Convert label to time ?

General discussion on OpenOffice Calc and its spreadsheets.

Convert label to time ?

Postby LiverPAN » Sat Oct 18, 2014 10:17 am

Hi Everyone,

I imported a table with several columns. One was START TIME 06/01/2013 12:30 PM where the /0l/ is the month. I managed to separate into two columns = START DATE | START TIME. I then managed to format the Start Date. I cannot format the START TIME.

CURRENT FORMAT = ' 06:27 PM instead of 06:27 PM.
How do I strip the apostrophe that is making it a label instead of time? Search & Replace failed. Formatting the cell failed. I have another column that is the end time so there's 143 entries x 2.

DESIRED OUTCOME:
A B C D
START DATE START TIME END TIME HOURS
01 JAN 2013 12:00 PM 1:00 AM 13:00 <<<==(C2<B2)+C2-B2 this is in HELP(Calculating Time Differences ) but untested formula

thanks,
LiverPAN
 
Posts: 24
Joined: Tue Mar 25, 2014 6:30 am

Re: Convert label to time ?

Postby Elwood M. Walker » Mon Oct 20, 2014 6:34 am

Hello,

You can't search and replace the apostrophe because it's not there. The leading apostrophe is only used in the entry box, to signal to Calc that the entry is text and should not be converted to a numeric value when entered and stored in the cell.

To convert your times from text to numbers ...
1) Select the cells you want to convert
2) Make sure that the cells are not formatted as text; Format > Cells ... should be Number/General or other number format.
3) Data > Text to Columns
Separator character: TAB (or something other character that does NOT appear in the data)
Click OK

thanks,
Elwood M. Walker
 
Posts: 33
Joined: Mon Mar 24, 2014 6:09 am


Return to OpenOffice.org Calc

Who is online

Users browsing this forum: Bing [Bot] and 1 guest

cron