Time without "AM" or "PM"

[moebfd]

Hi All,
Hope you are fine and well.
I would like to display the time in 12 hour format without the "AM" or "PM" (after the time).
Is there anyway to do this.
Kind regards

 

[Logit]

Right click cell

Format cells

Custom

h:mm

 

[Macropod]

So just use the custom format:
h:mm

 

[etaf]

you will have to change to text , there is not a format to do this as far as i know - as excel would need to know if 10:00 or 22:00 for time calculations

did you ask this as part of another question on excelforum.com ???

This will still use TIME - but only 0:0 to 12:59 - format as H:mm

=IF(A2>=13/24,A2-0.5,A2)
=MOD(A2-1/24,0.5)+1/24

Book4
	A	B	C
1
2	1:00	1:00	1:00
3	1:30	1:30	1:30
4	2:00	2:00	2:00
5	2:30	2:30	2:30
6	3:00	3:00	3:00
7	3:30	3:30	3:30
8	4:00	4:00	4:00
9	4:30	4:30	4:30
10	5:00	5:00	5:00
11	5:30	5:30	5:30
12	6:00	6:00	6:00
13	6:30	6:30	6:30
14	7:00	7:00	7:00
15	7:30	7:30	7:30
16	8:00	8:00	8:00
17	8:30	8:30	8:30
18	9:00	9:00	9:00
19	9:30	9:30	9:30
20	10:00	10:00	10:00
21	10:30	10:30	10:30
22	11:00	11:00	11:00
23	11:30	11:30	11:30
24	12:00	12:00	12:00
25	12:30	12:30	12:30
26	13:00	1:00	1:00
27	13:30	1:30	1:30
28	14:00	2:00	2:00
29	14:30	2:30	2:30
30	15:00	3:00	3:00
31	15:30	3:30	3:30
32	16:00	4:00	4:00
33	16:30	4:30	4:30
34	17:00	5:00	5:00
35	17:30	5:30	5:30
36	18:00	6:00	6:00
37	18:30	6:30	6:30
38	19:00	7:00	7:00
39	19:30	7:30	7:30
40	20:00	8:00	8:00
41	20:30	8:30	8:30
42	21:00	9:00	9:00
43	21:30	9:30	9:30
44	22:00	10:00	10:00
45	22:30	10:30	10:30
Sheet1

Cell Formulas
Range	Formula
B2:B45	B2	=IF(A2>=13/24,A2-0.5,A2)
C2:C45	C2	=MOD(A2-1/24,0.5)+1/24

 

[moebfd]

Thank you so much, that has worked.

 

[etaf]

you are welcome

 

[Macropod]

etaf,

Your complicated approach is quite unnecessary. As Logit and I posted, all you need is the custom h:mm time format.

 

[moebfd]

Hi Macropod
Unfortunately that did not work. When inputting a time of say 13:04 and changing the formatting to h:mm it would leave it as 13:04, where as I wanted it to display 1.04 with the AM and PM.
Kind regards

 

[Macropod]

OK, so how will you tell, then, whether the displayed time is am or pm?

 

[etaf]

@Macropod​

Your complicated approach is quite unnecessary. As Logit and I posted, all you need is the custom h:mm time format.

Well, that was to answer the OP question - OP does not want to show 13:00 to 24:00 and just see 1-12 again for PM times
hence the Complicated Approach

I did see this question in excedforums after a solution for a different question on time had been applied - but cant find it now