In excel: How to determine if 01/01/1900 then print out empty string, otherwise show the date

excel excel-2003
RetroCoder picture RetroCoder · Aug 27, 2011 · Viewed 19.2k times · Source

I'm trying to show all dates except for 01/01/1900. In excel, how do I first check for 01/01/1900 and then replace only that date with empty string or "", but leave any other dates alone? I also need to keep the format set to small date format of mm/dd/yyyy.

I tried to use replace, if, date, datevalue commands without successs.

Answer

adamleerich picture adamleerich · Aug 29, 2011

Another answer which does not affect your data, but only the presentation layer is to set up a custom formatting. Choose the cells you want, and press CTRL+1 or choose Format Cells from the Format menu. Then under number format, Date should be selected. Click on Custom at the bottom of the list, then add two semicolons after the formatting string picture that is there.

A custom formatting string can have three (sometimes four) pictures seperated by semicolons. The first is for positive numbers, the second for negative, and the third for zero.

My formatting string looks like this: 'm/d/yyyy;;'. All zero dates (1/0/1900) will be blank. No extra column needed.

Related questions

Loop through each row of a range in Excel

This is one of those things that I'm sure there's a built-in function for (and I may well have been told it in the past), but I'm scratching my head to remember it. How do I loop through each row …

Read More
Conditional Formatting (IF not empty)

How do I conditionally format a cell so if not blank it is grey? I tried to do 'not equal', but it didn't work. I am using Windows Office 2003 with Windows XP at work. I don't see the same feature …

Read More
How to show current user name in a cell?

In most of the online resource I can find usually show me how to retrieve this information in VBA. Is there any direct way to get this information in a cell? For example as simple as =ENVIRON('User') (which did …

Read More