Time Formats

[dbarrow]

have a co worker who would like to display time in an excel spread sheet, in 12 hour format -but without AM or PM following the time. Currently when she tries it she gets military time (23:20pm) what she wants is 11:20 Have tried several ways to format the cell but am not finding a good solution. Any ideas or suggestions are appreciated.

 

[rdw72777]

Can she manipulate the time value? Enter the formula below next to the time value and then format as hh:mm; should work, though its ugly. Assumes the initial value (23:20) is in cell A1.

=IF(ROUND(A1,0)>A1,A1-0.5,A1)

 

[Scott Huish]

With a formula:

=MOD(A1-0.5,0.5)

It will make all times AM however, in order to display as you want.

However both these formulas would make 12:01 PM 0:01

 

[Scott Huish]

This should work better:

=MOD(A1-0.5,0.5)+(0.5*(HOUR(A1)=12))

 

[dbarrow]

Thanks for the help