Microsoft Query: Formatting 01/01/1900 Dates as Blank

Dear John (don't worry, this is not a "dear john" letter),

Conditional formatting on the cells containing the dates (e.g. that column) could make the cell "appear" to be blank, if you make the foreground colour the same as the background colour (e.g. white).

Questions:
1. But would the appearance of blankness be sufficient, or are you needing them to really be blank?
2. What version of Excel are you using?
3. Would you agree that trying to handle this problem with formulas in the data cells is not going to work because the formulas will be replaced by the results of the SQL query?
4. Alternatively, would you be willing to "hide" the original date column (if it is a column) and have a 2nd date column which contains formulas which return blank when the date is 01/01/1900?  The formula could be something like: =IF(A1=1,"",A1)) and put that in B1 and copy it down, because the date 01/01/1900 equates to 1.
5. If none of the above helps, can you please attach an example spreadsheet which shows the data?

You can write a sub like the following

'This example scans a column of data named "myRange."
'If a cell has the value of 1 (1/1/1900) in the cell range, that cell will be replaced by BLANK ("")

Dim ws As Worksheet
Dim r As Range
myRange = "B22:B26"
Set ws = Application.ActiveWorkbook.ActiveSheet 'or be more specific on workbook and sheet

Set r = Range(myRange)
For n = 1 To r.Rows.Count
    If r.Cells(n, 1).Value = 1 Then
        r.Cells(n, 1).Value = ""
    End If
Next n
End Sub

Select allOpen in new window

To have it run when Excel refreshes and after you SQL query runs, you probably need what is described here:  http://stackoverflow.com/questions/26983053/how-to-call-macro-after-refresh-or-refresh-all-button-pressed

Hi tel2:

That's funny about the "Dear John" letter!  I'm happily married, so I've been fortunate not to have ever gotten one of those.  :)

I want to answer your questions, as follows:

1. But would the appearance of blankness be sufficient, or are you needing them to really be blank?  The appearance of the cells simply being blank is what I want.  It looks "ugly", to have a date of 01/01/1900.

 2. What version of Excel are you using?  Excel 2010 (32-bit).

 3. Would you agree that trying to handle this problem with formulas in the data cells is not going to work because the formulas will be replaced by the results of the SQL query?  I'm not sure, if I'm answering this correctly.  But, I'd prefer to have the issue taken care of prior to SQL placing the data in Excel.  It would be cumbersome to have to format all of the data, afterward, especially since (upon "refresh") the number of rows in the spreadsheet can dynamically change.

 4. Alternatively, would you be willing to "hide" the original date column (if it is a column) and have a 2nd date column which contains formulas which return blank when the date is 01/01/1900?  The formula could be something like: =IF(A1=1,"",A1)) and put that in B1 and copy it down, because the date 01/01/1900 equates to 1.  Normally, I would be okay with this.  But, not every date in a column is going to contain 01/01/1900, and I don't want to have to show my trainees how they have to take an extra step in Excel.  That's why I'd like to find a way to have Microsoft Query fix this.

 5. If none of the above helps, can you please attach an example spreadsheet which shows the data?  Thanks, for the offer.  But, we have confidential customer data here at our company and I try to prevent any "facetime", in showing such data on the internet.  (Sorry.)

John

Hi tel2:

I apologize, for not answering your other questions.  I was quite busy that day.

I'm from the United States (southern California).

xtermie's solution seems reasonable.  But, I try to stay away from VBA in Excel, for a lot of reasons.

Thanks!  :)

John

Hi John,

Thanks for the points.

What solution did you decide to use?  Conditional formatting or using the formula I gave you or something else?

In future I suggest you make your reluctance to use VBA clear in your original post, so experts don't waste their time going down fruitless paths, which I don't think is fair on them, especially if they don't end up getting any points for it.  A few words from you can save a lot of time for volunteer experts.  You might also end up getting more non-VBA options to choose from if experts know that's all you want.

Apart from that, it's been a pleasure doing business.

tel2

Hi tel2:

I simply decided to update my query directly in T-SQL.

Thanks, for the follow-up!  :)

John