12 Hour Time Format

[mplaut]

Is there any way to get time values to display in 12 hour format, that is without AM/PM information?

[mriosv]

Format cells / Number / Time, and select for example "HH:MM AM/PM"

[mplaut]

Thanks for your reply.

Choosing HH:MM AM/PM will give 01:30 AM for 01:30 and 01:30 PM for 13:30

I want a format that will give just 01:30 (or 1:30) for BOTH, without AM or PM.

[Hagar Delest]

What about HH:MM? Check the Numbers tab of the Format Cells dialog.

Thanks to add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.

[mplaut]

Your suggestion is very logical, and it is the first thing that I tried.

Alas, it gives the same result as choosing HH:MM AM/PM

That is, the AM/PM still shows. Perhaps it was intended to be a 12 hour format, and it looks like it should be, but it isn't. Maybe it is a bug???

[acknak]

Try the sample attached below...

[Robert Tucker]

Maybe an extra column:

=IF(B3-0.5>=0;B3-0.5;B3)

filled down.

time_formats-edit.ods

(9.84 KiB) Downloaded 352 times

[mplaut]

Thanks Robert Tucker, your suggestion is almost what I was after.

One modification that I thought improved it is to make the format H:MM (instead of HH:MM) so that the leading zero is dropped from the single digit times.

The only remaining problem is that 12 o'clock (both AM and PM) show as 0:00 instead of 12. That also means that 12:15 shows as 0:15 instead of 12:15.

[Robert Tucker]

I can't see how to solve that. I can't see that you can use functions like LEFT, MID and RIGHT on a time because they are stored as decimal numbers representing days, e.g. 06:00 is stored as 0.25, and even converting to text you just get 0.25 as text rather than the number 0.25. (Otherwise you could tell it to search for zeros at the start of the time and then output the following digits (and colon) accordingly.)

[TessaES]

If you change the first 0.5 to 13/24 only times bigger than 13:00 will be changed:
=IF(B3-13/24>=0;B3-0.5;B3)
or
=IF(B3>=13/24;B3-0.5;B3)
which looks a little clearer to me

[acknak]

... I can't see that you can use functions like LEFT, MID and RIGHT on a time because they are stored as decimal numbers...

It's a bit messy, but it can be done:
=SUBSTITUTE(SUBSTITUTE(TEXT(A3;"H:MMa/p");"a";"");"p";"")

I'd say either fiddling with the text, or changing the value, are about equally messy; it's a shame there's no format to do this. Displaying a time with no indication of am/pm seems like asking for trouble, but if that's what the user wants, why not provide it?

[Charlie Young]

Another possibility is to write a function, something like

Code: Select all

Function Time12Hour(TimeVal As Double) As String
	Dim h As Long
	Dim m As Long
	Dim s As Long
	Dim t As Double
	
	'Remove any date portion
	t = TimeVal - Int(TimeVal)
	
	h = Int(t * 24)
	m = Int(t * 1440) MOD 60 '1440 = minutes per day
	s = Int(86400 * t) MOD 60 '86400 = seconds per day
	
	'convert to 12 hours
	h = h MOD 12
	if h = 0 then
		h = 12
	endif
	
	Time12Hour = Str(h) & ":" & Right("00" & Trim(Str(m)),2) & ":" & Right("00" & Trim(Str(s)),2)
	
End Function

Then, for example, Time12Hour(Time(14;10;12)) indeed displays as 2:10:12. The format may be modified by fiddling with the last line.

I see I have some things to learn about formatting posts here, so I hope this is clear enough.

[mplaut]

I thank everyone for their efforts so far. I think the suggestions are very close, but I have the following comments.

TessaES: Certainly an improvement for 12 pm, but 12 am still comes up as '0'. This may be acceptable in a lot of applications.

Charlie Young: A very nice suggestion. When I tried it, I found that for some reason, 13:14:59 and 13:15:00 both result in 1:14:59.

13:15:01 comes out properly as 1:15:01

Acknak: There are applications where it is very obvious whether AM or PM is meant. For example where all the information relates to times during the day. In those cases readers find the AM/PM a distraction

Thanks very much so far.

[Charlie Young]

[quote="mplaut"]I

Charlie Young: A very nice suggestion. When I tried it, I found that for some reason, 13:14:59 and 13:15:00 both result in 1:14:59.

13:15:01 comes out properly as 1:15:01

That is an interesting thing. The time 13:15:00 as a double is 0.552083333333333 (to 15 places). Entering that into a cell, and referencing it with my function gives me 1:15:00 as hoped, but if I just chop off the last decimal place getting 0.55208333333333 (to 14 places), the function will report 1:14:59, but the 0.55208333333333 formatted as a time shows as 13:50:00. It's therefore debatable which is the accurate display. I'm going to look at this more closely later.

Note that one could get 1/100ths of a second out of the function (in fact that's related to why I wrote it in the first place!) by adding the line

s00 = Int(t * 8640000) MOD 100

and tagging the string version onto the end.

[Charlie Young]

Here is version 2 of the function, which seems to agree more closely with Calc's time formats. I have added the 1/100ths of a second, but it should be apparent enough how to modify it as desired. The use of CLng instead of Int in the s00 calculation seems to be a key thing, as it rounds up instead of truncating.

Code: Select all

Function Time12Hour00(TimeVal As Double) As String
	Dim h As Long
	Dim m As Long
	Dim s As Long
	Dim s00 As Long
	Dim t As Double
	
	'Remove any date portion
	t = TimeVal - Int(TimeVal)
	
	h = Int(t * 24)
	m = Int(t * 1440) - 60 * h
	s = Int(t * 86400) - 3600 * h - 60 * m
	s00 = CLng(t * 8640000) - 360000 * h - 6000 * m - 100 * s
	'convert to 12 hours
	h = h MOD 12
	if h = 0 then
		h = 12
	endif
	
	Time12Hour00 = Trim(Str(h)) & ":" & Right("00" & Trim(Str(m)),2) & ":" & Right("00" & Trim(Str(s)),2) & "." & Right("00" & Trim(Str(s00)),2) 
	
End Function

Just to be clear, to eliminate the 1/100ths, change Int to CLng in the s = line, and just get rid of everything after and including & "." in the function assignment. Also note that s00 can be eliminated altogether in that case.

[Charlie Young]

Well before anyone else said anything, I found a problem with my last version of the function. It could display a time with a 60 in the seconds field after applying my suggestion about eliminating the 1/100ths.

Try this version (no 1/100ths):

Code: Select all

Function Time12Hour(TimeVal As Double) As String
	Dim h As Long
	Dim m As Long
	Dim s As Long
	Dim t As Double
	
	'Remove any date portion
	t = TimeVal - Int(TimeVal)
	
	h = Int(t * 24)
	m = Int(t * 1440) - 60 * h
	s = CLng(t * 86400) - 3600 * h - 60 * m
	if s = 60 then
		s = 0
		m = m + 1
		if m = 60 then
			m = 0
			h = (h + 1) MOD 24
		endif
	endif
	
	'convert to 12 hours
	h = h MOD 12
	if h = 0 then
		h = 12
	endif
	
	Time12Hour = Trim(Str(h)) & ":" & Right("0" & Trim(Str(m)),2) & ":" & Right("0" & Trim(Str(s)),2)
	
End Function

Stay tuned...