I have imported a spreadsheet of times for sunrise and sunset from a planetarium program with the following format for each time: 07h39m38s, which I want to display as “7:39am.” This is an option under “cell format” then going to the “time” category and selecting “01:37pm” format after selecting the column where the times are located. However when I do this, the “07h39m39s” stays in the original format and this is true even when I select the column again and clearing all formatting before selecting the time format and trying to change it again. I have also tried the following: 1) when opening in Calc during the text import dialogue and it asks me if the following format looks OK, I have selected the column in question and changed it from “standard” column type to “text” or “US English” and it makes no difference, once again clearing all formatting. I am not certain whether the “Cartes du Ciel” program I’m importing the data from or the Libre Office is the source of this recalcitrance to change the time formats; all I know is that I have not updated the Cartes du Ciel program since last year when I converted the times in this manner that it worked beautifully and this year it doesn’t budge. Any ideas on how to proceed?
Hello @wilddouglascounty
The main point is - date and time are represented in calc as numbers (more information about it you can find here)
The value 07h39m38s is imported in your spreadsheet as text, not as time value. Clearing, applying and changing cell format does not change the real cell value, it only can change the way this value is displayed. So the text will stay text, even if you change format to number, decimal, time or whatever. In your case, you can select column with imported times and use Find&Replace to change “h” and “m” to standard delimiter “:” and “s” to empty string. Calc then will convert cell text to time value. Or you can use TIME() formula to perform transformation: assuming 07h39m38s string is in A2 cell, enter formula =TIME(LEFT(A2;2);MID(A2;4;2);MID(A2;7;2)) in B2 and the formula will return time value for the text given.
After text is converted to time value you can apply any time format of your choice.
Please find a demo spreadsheet with the transformations mentioned.
You nailed it, SM_Riga (see below)! I forgot that I had to do that last year as well. Since I already changed the format, after I swapped out the “h” and “m” with colons, it automatically converted the number string to the desired time format along with AM/PM. Thanks so much for the reminder! This conundrum is solved.