Solved

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

Posted on 2016-08-02
7
75 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 11

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 11

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 11

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

706 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

19 Experts available now in Live!

Get 1:1 Help Now