Getting the Formatted Value from Cells (1 Viewer)

[proballin]

I have dates in my excel sheet that originally were put in in the mm/dd/yyyy format. However I right clicked on it, did "Format Cell" and changed it to the "Jan-01" format. However when I do a comparison statement in VB it still compares the cells to the original format of mm/dd/yyyy and it messes up my statement. For example:

01/01/2009 is not equal to 01/05/2009, however Jan-09 is equal to Jan-09.

How do I get VB to see the cell's formatted version, not original.

 

[wazz]

i don't use excel much but i think it's safe to say that dates in VBA are never compared 'as they are formatted', but how they are stored; and dates are stored as numbers (type 'double' i believe). so the format should be irrelevant. er, i don't see what's wrong with your statements,
01/01/2009 is not equal to 01/05/2009, however Jan-09 is equal to Jan-09.

... oh, Jan-09 is Jan 2009? i thought it was Jan 9th.

i guess you'll have to compare using the DatePart function to compare months and years. if you're comparing dates without specification, the entire date will be compared, month, day and year, no matter what you "see". what you see is a formatted version of a complete date, but the complete date is still always there, and that's what VBA will use.

 

[proballin]

Gotcha...thanks!

 

[proballin]

I was able to get that date in VBA by doing:

Cells(i,j).Text

Adding the .Text works.

 

[boblarson]

I was able to get that date in VBA by doing:

Cells(i,j).Text

Adding the .Text works.

Be careful, the .Text doesn't always work. But, .Value should.

 

[proballin]

Really? Why is that?

Also .Value did not work for me. It would still return the full date as opposed to just the Month and the Year.

 

[boblarson]

Well, .Text might work in this case for you, but watch out it has burned me before. .Text will use the displayed information where .value will use the actual value.

So, maybe in your case it is what you needed, but just know what it does and when to use it.

 

[proballin]

Ohhhhh...I got ya. Yeah for my case I needed what was being displayed only, not the literal value. Good looking out though Bob!