Date Format Issue

[RemyLabue]

Ok, I recieve three excel sheets via Outlook everyday. The excel sheets are automatically created by querying a database. It's my job to take this excel sheet a put it in the correct format before sending it out to everyone else. Don't ask me why it isn't pulled in the correct format to begin with, I cry myself to sleep every night just thinking about it.

To do this as quickly as possible I created a VB script. This script copied the original sheet across multiple sheets and removes rows from each depending on what's in that specific row. I've completely automated everything with the exception of one tab. On this tab, I need to remove a row with a date not equal to cell J4 in column G. The problem here is, when the data is queried from the DB the dates are placed in this column in a General format, not date. The date that is in J4 changed everyday, so the formula for this cell is TODAY(). I can't just change the format of column G to Date format, because excel still doesn't see Column G and J4 as equal to each other. Any ideas on how I can convert Column G to the correct format to compare to cell J4? Maybe Vice-Versa?

 

[big_mac]

Add this to your macro
Columns("G:G").NumberFormat = "mm/dd/yyyy"

 

[RemyLabue]

All that does is reformat that column to the Date format.. That's not solving my problem. That column comes to me as a General format, I can click on it and change it to the date format manually, but even if I do that, I still can't get another cell to equal it truthfully. It's kind of hard to explain. I can't use the HTML maker with it due to errors with 2K7, if someone could give me a way to send it to them, you may be able to see the issue yourselves..

Basically, Column G, even after being reformatted to Date, still won't equal TODAY(), and it should.

 

[jindon]

Hi

Seems the data in Col.G is Text.
try

1) Select Col.G
2) go to [Data] - [TextToColumn] then click "Finish"
3) Format as you want

 

[RemyLabue]

That worked! You're a genius. Thanks alot..

I was really frusterated due to the fact that I could write a countif formula with a TODAY() method and have it return a number accurately, but I couldn't get the two cells to equal accurately... Thank again.

 

[Datsmart]

The Format of the cells is not important. What is important when comparing dates is that they both are truly dates. Excel see's dates as "Serial Numbers".
Excel sees August 1st, 2007 as 39295.
Excel sees 08/01/2007 as 39295.
If your data comes in as text, you can not compare it to a date without converting it to a true serial Date.

You didn't post any sample "Dates" to show what the database dates look like. Here is a suggestion to try and convert your Text Dates to true dates.

Suggestion 1. (convert by Text to Columns)
Select column of Text dates you want to convert.
Click Data | Text to Columns….
Click Next to get to 3rd page of Wizard
Under "Column data format" choose "Date" and then click dropdown arrow
Choose format of Text you are converting from. ie:"YMD" for Year Month Day or whatever the text looks like.
Click Finish

Suggestion 2. (convert by addition)
Select a blank cell
Hit Ctrl-C to copy
Select your number range
Right Click and choose Paste Special
Click Add, then OK.
Then Format the cells as date.

Suggestion 3. (If you have Excel 2003)
Select the cells affected and right click on the yellow "!" that shows up next to the cells and choose convert to number.
(Must have "Number stored as Text" checked in Tools|Options|Error Checking)

Once we know what style your dates are coming in as code can be created to automate the conversion.