Re: [libreoffice-users] text fields to time fields

Don't know where you are on this at the moment but, apologies, I overlooked
using the a.m./p.m. indicator in the text string.

A solution that does not overlook that is to use a formula in an adjacent
cell to convert the text value to a time value and then format that time
value to display as you like. You can then hide your column with text time
values and display only the column with the time value formatted as you
want.

As follows..
Text in cell A1, e.g. 11:00:00 a.m.

Formula in cell B1, e.g.
=IF(REGEX(A1,"(.*)( p.m.| a.m.)","$2")=" p.m.",TIMEVALUE(REGEX(A1,"(.*)(
p.m.| a.m.)","$1"))+0.5,TIMEVALUE(REGEX(A1,"(.*)( p.m.| a.m.)","$1")))
Format the result as you want it to display.

What's in the formula...
The formula presumes all text string times either end with " a.m." or "
p.m."
The =IF() tests whether the time text has the string " p.m" in it. If it
doesn't have " p.m." then it's presumed to have " a.m."

The test uses =REGEX() to parse out the " a.m." or " p.m." string and save
it to a REGEX capture group, "$2". The capture groups are those parts of
the REGEX expression inside parentheses.
In this case the expression is "(.*)( p.m.| a.m.)". It has two capture
groups, two sets of parentheses.

The way you enter your text times is a valid time format without the "
a.m." or " p.m." at the end. Take the time portion of your text, the "$1"
capture group, and convert it to an actual time value.
TIMEVALUE(REGEX(A1,"(.*)( p.m.| a.m.)","$1"))

In the IF test if "$2" capture group equals " p.m." then 12 hours needs to
be added to the time otherwise it is used without adding hours.

So finally if $2 is " p.m." calculate the time as TIMEVALUE(REGEX(A1,"(.*)(
p.m.| a.m.)","$1"))+0.5

Otherwise calculate the time as TIMEVALUE(REGEX(A1,"(.*)( p.m.|
a.m.)","$1"))

Then format the cell with the desired time format.

Hope I haven't overlooked something this time.

On Wed, Jun 28, 2023 at 5:31 PM James <bjlockie@lockie.ca> wrote:

> That seems to leave 11 pm as 11:00 (I expect 23:00).
> Maybe I am doing it wrong.
>
> On 2023-06-28 17:10, Alan B wrote:
> > Select the text fields, hopefully they are in a column or columns.
> >
> > After selecting the fields select "Data > Text to Columns..." from the
> menu
> > In the Text to Columns dialog box select the following options:
> > For "Separator Options" tick "Separated by".
> > The only separator option that should be ticked is "Space".
> > Be certain "Detect special numbers" is ticked in the "Other Options"
> > choices.
> > Click the "OK" button.
> >
> > The time portion of the column will now be a time formatted number in the
> > column it was already in. Depending on the default format it may or may
> not
> > have AM/PM. If it doesn't, just change the time format to what you need
> at
> > this point.
> >
> > The "a.m." or "p.m." portion of your original text will be moved one
> column
> > over. It will overwrite what is in the next column if the column is not
> > empty.
> >
> > To prevent adjacent columns from being overwritten, while still in the
> > "Text to Columns" dialog box, go to the "Fields" section at the bottom.
> >
> > There should be two columns with a heading of "Standard", one column
> > showing the time portion of your text, the other column showing the
> a.m/p.m
> > portion. Click the word "Standard" over the a.m/p.m column.
> >
> > Now open the "Column type" list and pick the "Hide" option.
> >
> > Finally, click OK to close the dialog box.
> >
> > You text time will now be a time number in the column it was already in.
> > The a.m/p.m portion has not overwritten the adjacent column as it has
> been
> > discarded as part of the Text to Columns operation.
> >
> >
> > On Wed, Jun 28, 2023 at 4:32 PM James <bjlockie@lockie.ca> wrote:
> >
> > > I have text fields like:
> > >
> > > 10:06:59 a.m.
> > > 8:57:36 p.m.
> > >
> > > How do I convert them to 24-hour time fields?
> > > --
> > > To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
> > > Problems?
> > > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > > Posting guidelines + more:
> https://wiki.documentfoundation.org/Netiquette
> > > List archive: https://listarchives.libreoffice.org/global/users/
> > > Privacy Policy: https://www.documentfoundation.org/privacy
>
>
> --
> To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy


-- 
Alan Boba
CISSP, CCENT, ITIL v3 Foundations 2011

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy