Difficulties changing a cell formatted as text (07:52) to 'Time' (HH:MM AM/PM)

[PaulHam]

I import a series of non-standard timestamps into a CALC spreadsheet, each is formatted as text.
Here is an example of a raw timestamp

1/01818:07:52:50:65781 (n/ddddd:hh:mm:ss:msec)

The n/ has no meaning and can be ignored, ddddd is a day count and can be ignored, hh is hours, mm is minutes, ss is seconds and could be ignored, and msec is mili seconds and can be ignored

I’m using the MID function to remove the unwanted characters, leaving what I need 07:52 (hh:mm)

However the result is still formatted as text, and I would like to format the cell as time, so I can format it as it as HH:MM AM/PM. Is there a way to reset the format from text to time in CALC? I’ve tried many things using the format menu, but none change the format, it remains as text

Thanks in advance for any assistance in this matter

Version: 26.8.0.3 (X86_64)
Build ID: bce0998afefdbc355585ca324285661a2170ba77
CPU threads: 4; OS: Windows 10 X86_64 (build 19045); UI render: Skia/Raster; VCL: win
Locale: en-GB (en_MY); UI: en-GB

[Zizi64]

Plase upload an PDF type sample file here.

The TYPE and the FORMAT are different properties of the cell content. You can not change the type of a data by formatting. If a data located a cell has TEXT type, you can not format it as a NUMERIC date value.

[Wanderer]

@PaulHam

I’ve tried many things using the format menu, but none change the format, it remains as text

Fornat menu changes, how something is displayed, if type fits. It never converts data. So a phone number '030 12345 stays text, if you play around with format menu.
And a text-function like MID returns a string.

So you have to tell calc to convert data via function, after using MID.
For example =VALUE(MID(A1;3;5)) to retrieve the day-number or =VALUE(MID(A1;9;5)) for the time. The latter should return a fraction of a day and can be formatted as time.

https://help.libreoffice.org/latest/sq/text/scalc/01/func_value.html

[Wanderer]

@PaulHam

I import a series of non-standard timestamps into a CALC spreadsheet, each is formatted as text.

Completely different way to handle this:
Usually on import you are presented with an dialogue to configure import. Don’t skip this, but tell Calc (via the box fir additional separators to the right) this text is to be splitted at characters / and : The result is the a table of numbers, not text. So you can now do math on the numbers and calculate your times.

As there is also an option for fixed width you can also directly import the columns by counting characters.
A picture is in the following thread:

How to set column width by charactor rather than measurement? Can do this in excel

Maybe you can fill all the data, export as csv with fixed column width, then import this csv with fixed width, deleting the columns with excess data (shown in red in the screenshot). [import csv fixed width]

[PaulHam]

Wanderer,

Perfect!

I was clearly barking up the wrong tree with trying to format that text string. =VALUE added to my formula provided me with the exact result I was looking for.

Many, many thanks, I truly appreciate this place it has helped me a few times now. I should have come here sooner, but I do like to fix these myself, but on this one I wasted hours.

Best regards,

Paul

[PaulHam]

Many thanks again Wanderer, this could be a valuable option for another cell that I have a similar import issue with. Presently I can fix the that issue by a simple find and replace, prior to importing it. But I am hoping that I can adjust your earlier solution (Value) to make that work as well, that way I can import the raw data and modify in a formula to get a full timestamp

Many thanks once again

[PaulHam]

Just to update =Value also modified a UTC formatted timestamp containing text in a suffix using VALUE after the LEFT function removed the text…

Thank you once again as I can now import the raw data without having to take a number of steps to convert it prior to import

[Lupp]

disask_137497_inacceptableFormats.ods (21.6 KB)