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

Hello:

I have a SQL query that I have placed into a Microsoft Query, within Excel.

I'd like to have Excel return blank dates, upon refresh, for those dates that are 01/01/1900.  

Again, I want this change to occur upon refresh in Excel.  I don't want to program the change in my SQL query.

How can I do this?

Thanks!

John
John EllisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tel2Commented:
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?
xtermieCommented:
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

Open 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
John EllisAuthor Commented:
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
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

tel2Commented:
Hi John,

Thanks for your answers.  Good to hear your marriage is going well.  Mine's not too bad, either.

You seem to be happy with the conditional formatting option.  Note that if you go this way, then each PC that it is used on should have the conditional format font colour set to whatever the Windows background colour is for that PC.  Most people probably have white (the default) as their Windows background colour, so this might not be a problem.  If you ignore this issue, and someone has a grey Windows background, they'll see white text on a grey background for those 01/01/1900 dates, which might not be a big issue anyway.
If you don't know how to set up conditional formatting for a column, then send me the spreadsheet and I'll do it for you.  I'm not confident to talk you through it because I don't have Excel 2010, (and I'm too lazy).  I don't expect you to send confidential info.  Remove the confidential parts and replace them with a couple of records of dummy (made up) data.

Regarding my hidden column alternative solution, I'm not sure what the problem is.  Whether you go with the conditional formatting solution or the hidden column solution, the spreadsheet needs to be set up in advance (I guess that's the "extra step" you talk of).  I assume you'd set up one template (either with conditional formatting or the hidden column + formula column) and then take copies of that spreadsheet, right?

Alternatively, what do you think about xtermie's solution?

What country are you in, anyway?  I'm in New Zealand.

tel2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John EllisAuthor Commented:
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
tel2Commented:
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
John EllisAuthor Commented:
Hi tel2:

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

Thanks, for the follow-up!  :)

John
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.