24 hour time format help with 00:00 calculation

[pogo]

whenever I place 00:00(12am)in a cell, excel still calculates the cell
information as 00:00 but the cell remains blank, as if nothing is in it. how
do I make excel show "00:00" in that cell . it only happens when I use 00:00
in my form.
Thanks in advance

 

[Fred Smith]

Go to Tools>Options>View and check Zero values.

 

[pogo]

I am sorry I meant to say that I only want the zeros to appaer in only one
column, if I check the zero box in options it clutters up the form by filling
in zero's in all of the empty cells. How would I have the zeros fill into
only specic cells on the form and not any others? sorry I was so vague
earlier.

 

[Fred Smith]

Well, that's trickier.

It begs the question, why do you have zeros in empty cells? Why not have blanks?
That, by far, would be the easiest solution.

If that's not possible for whatever reason, post back, and we'll look for
another solution.

 

[pogo]

I have cells a1 and a2 that I use to figure out various sums, differences,
and other aspects of times(military). The person using the form enters a time
in a1( a start time) then they enter a stop time in a2. they use miltary time
to enter the times. When the person enters 00:00 in cell a1( simulating 12
in the morning) and just say 15:00 in cell a2(stop time simulating 3pm), the
calculations still happen as excel knows what was typed in, but cell a1
remains blank. cell a2 shows the numbers 15:00. for some reason when entering
military time and you use 00:00, excel blanks out the cell that you typed
00:00 into. If I entered 00:01, then those numbers would show in cell a1, but
then the form wouldnt do its purpose. I need cell a1 to show that 00:00 was
entered and actually show the numeral"00:00". this allows outsiders and
insiders to know that somthing was started at 12am. The form also needs to
remain using military time. I tried but couldnt figure it out.there is always
someone smarter out there. Please help?
Thanks

 

[Sandy Mann]

Would pre-formatting the cell as text do what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[pogo]

great try but whenever I do that it shows up blank also
please keep trying though. I cant give up on it

 

[Biff]

I am sorry I meant to say that I only want the zeros to appaer in only one

column, if I check the zero box in options it clutters up the form by
filling
in zero's in all of the empty cells. How would I have the zeros fill into
only specic cells on the form and not any others? sorry I was so vague
earlier.

Go to Tools>Options>View and check Zero values.

In cells where you don't want the 0's displayed use this custom format:

0;-0;;@

Biff

 

[pogo]

thanks for the good try biff, but when I enter that custom code in the
worksheet it did not dispay the zeros but it also did not calculate the other
times that were not zero.
I also tried to use conditional formatting on the cells that showed zeros
that I did not want, when the zero optins box was checked. In conditional
formatting i said that if a cell =a reference cell that =0 then make the font
and the background color in the cell both white.That worked great the cells
that I wanted to show 00:00 in showed 00:00. The ones that I did not want to
show the zeros did not. The only problem was when I printed the sheet, the
zeros that did not show up on the form still printed out as zeros on the
printed form. Can anyone think of a fix for that problem. If so then I can
solve my dilema.
Thanks for the time and effort involved

 

[Sandy Mann]

I don't mean re-format the cell as text witht he dtat still in, I mean
format the cell as text then re-enter the data.

It works for me

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk