How can I get 12:00 am to print with zeros suppressed?

[Guest]

On a timesheet where the options are set to suppress zero values, 12:00 AM
will not print at all. How can I overcome this without having a bunch of
extraneous zeros everywhere?

 

[duane]

format as custom and specify h AM/PM - this will result in 12 A

 

[duane]

or perhaps you want h:mm AM/PM to see 12:00 A

 

[Guest]

This isn't really a solution I'd go with, but ...
Are you displaying seconds?
If not, Enter midnight as .00001. As I said, I wouldn't use this if someone
has something better, but it will display midnight in a cell.

tj

 

[Sandy Mann]

Jani,

12:00 AM is zero time so the only thing that I can suggest is to make the
entry very slightly not zero. With 00:00:00 showing in the formula bar I
added 1/1000 of a second to make it 00:00:00.001 this made the *12:00 AM*
appear again in the cell. No doubt if you have enough formulas it may
result in a very small error.

HTH

Sandy

ps after the addition of 1/1000 of a second and retruning to the cell, the
formula bar still showed 00:00:00

 

[JE McGimpsey]

Instead of suppressing zeros globally, choose a format for your non-time
cells that doesn't display zeros. For instance:


Format/Cells/Number/Custom General;General;;@


By default, XL's format strings have 4 parts - the first part for
positive numbers, the second for negative numbers, the third for zero,
and the fourth for text. In this case, leaving the zero field blank
will hide any zero values (the @ represents the text entered in the
cell).

 

[Fred Smith]

Date/time cells are a combination of day (the integer before the decimal
point) and time (the fraction after the decimal point). Currently your cell
has 0.00 in it, which is 12 am on day zero. You could use any day other than
zero (for example, 1.00). This will print as 12 am in time format, but will
not be suppressed because the amount is non-zero.