Date Format Issue

RemyLabue

New Member
Joined
Feb 1, 2006
Messages
44
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?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

big_mac

Board Regular
Joined
Jul 21, 2006
Messages
167
Add this to your macro
Columns("G:G").NumberFormat = "mm/dd/yyyy"
 

RemyLabue

New Member
Joined
Feb 1, 2006
Messages
44
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

New Member
Joined
Feb 1, 2006
Messages
44
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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

Forum statistics

Threads
1,181,055
Messages
5,927,861
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top