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
[Solved] Display time with am or pm
[Solved] Display time with am or pm
Last edited by MrProgrammer on Wed Sep 30, 2020 6:40 pm, edited 3 times in total.
Reason: Tagged ✓ [Solved]
Reason: Tagged ✓ [Solved]
OPENOFFICE 4.1.7 ON mac 10:15:4
- MrProgrammer
- Moderator
- Posts: 4895
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: am or pm
Hi, and welcome to the forum.
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
Select the cells. In the Format → Cells dialog, or preferably in the Format → Styles and Formatting dialog, set the number format as shown. Accessing and applying stylesdafyddapw wrote:I want to choose whether a time is am or pm from a column of random 24 hr times.
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).
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).
Re: Display time with am or prm
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!
thanks for your welcome too!
OPENOFFICE 4.1.7 ON mac 10:15:4
Re: Display time with am or 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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Display time with am or 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.
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.
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.
Re: Display time with am or 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
best wishes,
DW
OPENOFFICE 4.1.7 ON mac 10:15:4
Re: Display time with am or pm
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.
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
Re: Display time with am or pm
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)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
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.
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.
Re: Display time with am or 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.)
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
---
Lupp from München