[Solved] Display time with am or pm

Discuss the spreadsheet application

[Solved] Display time with am or pm

Postby dafyddapw » Mon Apr 06, 2020 2:51 pm

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
dafyddapw
 
Posts: 3
Joined: Mon Apr 06, 2020 2:49 pm

Re: am or pm

Postby MrProgrammer » Mon Apr 06, 2020 4:14 pm

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 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3908
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Display time with am or prm

Postby dafyddapw » Mon Apr 06, 2020 6:22 pm

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
dafyddapw
 
Posts: 3
Joined: Mon Apr 06, 2020 2:49 pm

Re: Display time with am or pm

Postby Villeroy » Mon Apr 06, 2020 8:42 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29293
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Display time with am or pm

Postby Zizi64 » Mon Apr 06, 2020 9:45 pm

...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; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9725
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Display time with am or pm

Postby MrProgrammer » Tue Apr 07, 2020 5:09 pm

dafyddapw wrote:I want to choose whether a time is am or pm from a column of random 24 hr times.
I had difficulty understanding your question. One cannot choose if a time is am or pm when given a 24-hour value. Either It is pm or it isn't. I believed you wanted to display whether a time is am or pm. How would I have guessed from your initial post that you want to copy the data to two new columns?

dafyddapw wrote:I wanted to try find a formula that would put am in a particular column and pm in another?
Additional questions:
• Do you need a formula, or are you really looking for way to split the times into two columns, whether by formula or not?
• Do you want the am/pm times condensed together in the columns, or do you want them aligned in the same row that the 24-hour time occupies?

I sense XY Problem here. For example, you should not pre-suppose the use of a formula for this task. Explain clearly what your goal is. Attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Provide both the original data (say, a dozen rows) and then type desired arrangement in your two new columns. Ensure that your example is a representative illustration for your task. State the scope of the real task. Is it 20 rows or 200,000 rows? The solution may be different for those cases. I have little patience for people who give us a sample, we respond, and then they say "well, that doesn't help because what I really have is …".
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3908
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Display time with am or pm

Postby dafyddapw » Tue Apr 07, 2020 9:45 pm

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
dafyddapw
 
Posts: 3
Joined: Mon Apr 06, 2020 2:49 pm

Re: Display time with am or pm

Postby S0S » Fri Jul 17, 2020 5:22 am

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
S0S
 
Posts: 2
Joined: Fri Jul 17, 2020 3:06 am

Re: Display time with am or pm

Postby Zizi64 » Fri Jul 17, 2020 7:57 am

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; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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
Zizi64
Volunteer
 
Posts: 9725
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Display time with am or pm

Postby Lupp » Fri Jul 17, 2020 2:01 pm

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 37 times
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 3002
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 24 guests