Solved

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

Posted on 2016-08-02
7
95 Views
Last Modified: 2016-08-08
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
0
Comment
Question by:John Ellis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 12

Expert Comment

by:tel2
ID: 41739844
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?
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41740282
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
0
 

Author Comment

by:John Ellis
ID: 41740548
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
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 12

Accepted Solution

by:
tel2 earned 500 total points
ID: 41741514
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
0
 

Author Comment

by:John Ellis
ID: 41746446
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
0
 
LVL 12

Expert Comment

by:tel2
ID: 41746501
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
0
 

Author Comment

by:John Ellis
ID: 41747195
Hi tel2:

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

Thanks, for the follow-up!  :)

John
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question