Solved

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

Posted on 2016-08-02
7
81 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
  • 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 17

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
grouping logic 6 49
How to base a filter depending on fields contents? 15 50
Excel macro runs twice 13 51
VBA EXCEL 2013, 2016 for Auto Sort upon fill in. 4 11
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

867 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now