Conditional Date Format.

[Kevin Burton]

I would like to conditionally format a date. I have read earlier posts on
conditionally formatting numbers but I have been unable to transfer this to
dates. Here is what I want and maybe someone out there has done this.

If the date is before some cutoff point say 1970, I want the format to be
blank, otherwise format the date in RED as m/d/yyyy.

Is this doable?

Thank you.

Kevin

 

[Dave Peterson]

You can't blank out the value within the cell using format|conditional
formatting.

But you could use format|conditional formatting to make the cell look
empty--white fill on a white background.

Select the cell
format|conditional formatting
value is:
Less than
=date(1970,1,1)

And give it that nice white on white format.

====
You could also use a custom format:
[White][<25569]General;mm/dd/yyyy

25569 is the serial date for 1/1/1970 when using the 1900 base date system.

 

[Kevin Burton]

Thank you. Thank you. This is exactly what I needed. I like the custom format
option. How did you get the serial number for 1/1/1970? If I read this right
the custom format says if the date is before 1/1/1970 then use "General"
format with the text "White". Right? Why is the color before the conditional
statement? Or that is just the way it is. Then if the date is after 1/1/1970
format it as mm/dd/yyyy. How can I be assured that the background will be
white also so it will appear blank? What if some style is applied to the cell
and the background is black or some other non-white color?

Thanks again.

Kevin

You can't blank out the value within the cell using format|conditional
formatting.

But you could use format|conditional formatting to make the cell look
empty--white fill on a white background.

Select the cell
format|conditional formatting
value is:
Less than
=date(1970,1,1)

And give it that nice white on white format.

====
You could also use a custom format:
[White][<25569]General;mm/dd/yyyy

25569 is the serial date for 1/1/1970 when using the 1900 base date system.

I would like to conditionally format a date. I have read earlier posts on
conditionally formatting numbers but I have been unable to transfer this to
dates. Here is what I want and maybe someone out there has done this.

If the date is before some cutoff point say 1970, I want the format to be
blank, otherwise format the date in RED as m/d/yyyy.

Is this doable?

Thank you.

Kevin

 

[Bob Phillips]

Thank you. Thank you. This is exactly what I needed. I like the custom
format
option. How did you get the serial number for 1/1/1970?

Put 1/1/1970 in a cell, then format it as General, you will see the n umber.

If I read this right
the custom format says if the date is before 1/1/1970 then use "General"
format with the text "White". Right? Why is the color before the
conditional
statement? Or that is just the way it is.

Yes that is so. And yes that is the way it is. If you enter it the other
way, Excel swaps it around.

Then if the date is after 1/1/1970
format it as mm/dd/yyyy. How can I be assured that the background will be
white also so it will appear blank? What if some style is applied to the
cell
and the background is black or some other non-white color?

To ensure that it is white, add conditional formatting to the cell with a
condition of say

=A1>=--"1970-01-01"

 

[Kevin Burton]

Thank you for the reply.

I am not sure how to read =A1>=--"1970-01-01". Would you be able to put this
in context of the original format? ([White][<25569]General;mm/dd/yyyy).

Also what is the difference between mm/dd/yyyy and m/d/yyy?

Thank you.

Kevin

 

[Dave Peterson]

Bob was suggesting an alternative to the format|conditional formatting rules
that I suggested in the previous post.

This wouldn't be used in the custom number format.

The difference is the way leading 0's are displayed in the month and day
fields. (I'm assuming you meant m/d/yyyy (4 character year).

Thank you for the reply.

I am not sure how to read =A1>=--"1970-01-01". Would you be able to put this
in context of the original format? ([White][<25569]General;mm/dd/yyyy).

Also what is the difference between mm/dd/yyyy and m/d/yyy?

Thank you.

Kevin

Put 1/1/1970 in a cell, then format it as General, you will see the n umber.




Yes that is so. And yes that is the way it is. If you enter it the other
way, Excel swaps it around.



To ensure that it is white, add conditional formatting to the cell with a
condition of say

=A1>=--"1970-01-01"

 

[Bob Phillips]

Bob was suggesting an alternative to the format|conditional formatting
rules
that I suggested in the previous post.

This wouldn't be used in the custom number format.

Because the custom format affects the text colour, not the background
colour.