[Solved] Display time with am or pm

Discuss the spreadsheet application
Post Reply
dafyddapw
Posts: 3
Joined: Mon Apr 06, 2020 2:49 pm

[Solved] Display time with am or pm

Post by dafyddapw »

Hi, I want to choose whether a time is am or pm from a column of random 24 hr times. Is there some way I can do this?
thanks DW
Last edited by MrProgrammer on Wed Sep 30, 2020 6:40 pm, edited 3 times in total.
Reason: Tagged ✓ [Solved]
OPENOFFICE 4.1.7 ON mac 10:15:4
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: am or pm

Post by MrProgrammer »

Hi, and welcome to the forum.
dafyddapw wrote:I want to choose whether a time is am or pm from a column of random 24 hr times.
Select the cells. In the Format → Cells dialog, or preferably in the Format → Styles and Formatting dialog, set the number format as shown.
Screen Shot 2020-04-06 at 09.08.07.png
Accessing and applying styles

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
dafyddapw
Posts: 3
Joined: Mon Apr 06, 2020 2:49 pm

Re: Display time with am or prm

Post by dafyddapw »

Many thanks, I wanted to try find a formula that would put am in a particular column and pm in another?
thanks for your welcome too!
OPENOFFICE 4.1.7 ON mac 10:15:4
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Display time with am or pm

Post by Villeroy »

Just do what MrProgrammer says and change the format (the appearance) of your time values and read the tutorial.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Display time with am or pm

Post by Zizi64 »

...and study and use the Cell Styles instead of the manual direct formatting method.

The Styles are the most valuable features of the AOO/LO office suites.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
dafyddapw
Posts: 3
Joined: Mon Apr 06, 2020 2:49 pm

Re: Display time with am or pm

Post by dafyddapw »

Thank you all for your replies. They are much appreciated. However, I sense a degree of irritation probably due to my rather vague question. I think I shall look at as many tutorials as I can find, and try to find a solution to my inexact problem.
best wishes,
DW
OPENOFFICE 4.1.7 ON mac 10:15:4
S0S
Posts: 5
Joined: Fri Jul 17, 2020 3:06 am

Re: Display time with am or pm

Post by S0S »

Perhaps this will help.

My understanding is that when there is only a 24 hour TIME value (12:00:00am to 23:59:59pm), it is saved as a positive decimal (with integer zero):
12 AM = midnight = 0.00000
12 PM = noon = 0.50000

So if a time value is in cell A1 :
a) To get an AM or PM into cell B1 , try this formula in cell B1 : =IF(A1<0.5;"AM";"PM")
b) To get an AM in cell C1 only if the time is AM, try this formula in cell C1 : =IF(A1<0.5;"AM";"")
c) To get a PM in cell D1 only if the time is PM, try this formula in cell D1 : =IF(A1<0.5;"";"PM")

If the time value in cell A1 is a full date and time, or a time value of more than 24 hours, my understanding is that the date portion (or excess time in blocks of 24 hours) is stored as the integer part of the number, AND if the date is before 30 Dec 1899, this will be a negative number.

To show an AM &or PM as above, these would need to be taken into account (removed) so as to leave the proper decimal (with integer zero) portion.

So, try these formulas to get AM or PM as above:
a) B1 : =IF(ABS(A1-INT(A1))<0.5;"AM";"PM")
b) C1 : =IF(ABS(A1-INT(A1))<0.5;"AM";"")
d) D1 : =IF(ABS(A1-INT(A1))<0.5;"";"PM")

Alternatively to just display AM or PM instead of the time in cell E1, and if the Date/Time value is in cell A1 :
1) try this formula in cell E1 : =A1
2) then Left Click cell E1, choose option: Format Cells...
3) In the Format Cell popup:
a) Choose the Category: Time
b) at the bottom put in the Format Code as: AM/PM
c) click the OK button

Please be aware that I do not have a deep understanding of most aspects of dates/times/OpenOffice calc. This is just a layman's attempt to help you solve your simple question with a hopefully simple solution that should cover most normal cases.
OpenOffice 4.17 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Display time with am or pm

Post by Zizi64 »

Hi, I want to choose whether a time is am or pm from a column of random 24 hr times. Is there some way I can do this?
thanks DW
If those "time" values are real time values (numerical date-time values with zero value of the date part), then you can examine that the value is larger or smaller than 0.5 (0.5 is a half of day = 12:00 in a 24 hour system)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Display time with am or pm

Post by Lupp »

It may, of course, sometimes be necessary to worry about time values "communicated" in AM/PM style.
There may be old data or data provided by somebody ignoring standards due to his principles. We then need to convert them to a usable representation, e.g.

In every real-life application exclusively ISO 8601 compliant (at least roughly) representations should be used.
There are issues forcing us to waste many many hours with "localized" date-time formats otherwise.

Following my guess what the original poster had in mind, I made the little demo attached here.
(Row numbers 11 and higher, mainly.)
Attachments
aoo101570playingWithTimeFormats_0.ods
(19.38 KiB) Downloaded 141 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply