I have a field with date and time ( format - mm-dd.yyyy hh:mm A/PM) and I would like to sort it based on time. I was able to split it into date and time cells using MID. I copied the time data to a different cell and tried to change time format to - hh:mm (24 hour format). The problem is in the split field with the time, the first character that shows up when I select the time field is a single quote. If I manually delete the quote mark the field will show time in 24 hour format (i.e 13:26). How can I change the text time field to not show the quote mark? There are 1000+ rows where this needs to happen.
When you import those text values (assuming you don’t type them), mark the column in the preview table as “Date (MDY)” and everything should import as true timestamp values.
Date is the number of days since 1899-12-30 (default), time is just the fraction of a day so we can use just the fractional portion for time
In an existing spreadsheet, instead of your MID formula, you could enter =MOD(A2,1) and format it as HH:MM
The quote mark shows there is actually text in your cell. In this case because you used a function for text MID(). If you feed the result to function VALUE() it tries to convert it to a number.
.
As Villeroy and EarnestAl showed, it can be avoided here, but you may find it useful in other places:
https://help.libreoffice.org/latest/sq/text/scalc/01/func_value.html
Thank you. It worked just like I want.