Number Conversion

B

badpuppie

I download a report from an online query I have no access to, meaning I
can't manipulate the query. The query returns reports with the date
they were completed in a yyyymm format (or so I thought). I maintain a
log as well and want to compare the two lists. When I run a comparison,
I convert my dates to yyyymm but the vlookup function doesn't work
because the dowloaded report only looks like its in a yyyymm format.
For instance, the dowloaded report looks like: 200312, which is Dec
2003. But when I convert it to date yyyymm it changes to244806 so it
is in some other format that won't allow me to convert it so that I can
compare the two lists in automation. How do I get the dowloaded 200312
to look the same way in a date format?

Thank you.
 
N

N Harkawat

In a helper column add the following
=date(left(a1,4),right(a1,2),1)

This will change all your yyyymm to 1st of that month and year
Now you can do the comparision with this helper column
Or
incorporate this formula within your vlookup to avoid the use of additional
helper column
 
B

badpuppie

that formula returns a value of 3/1/3850 for a cell that reads 200505.
The problem is I don't know what format or code that 200505 is in and
can't seem to convert it accordingly
 
B

BenjieLop

If your cell has 200505, how do you want it to be converted? Do you wan
it converted to *May 2005?
 
S

swatsp0p

Base on the OP's original post and the problems they are having with a
proven formula, I would guess the data that has been imported has some
leading or trailing spaces. This would mess with the output of the
formulas.

Try the =TRIM() function to strip off any spaces....
 
B

badpuppie

i want 200505 converted to 200505, the problem is it looks like 200505
but is something else. so when i run a vlookup function to compare
that date with my date i get an inaccurate output b/c excel doesn't
read the 200505 as a date, so when i convert it to a date i get that
strange return i previously posted.
 
B

BenjieLop

badpuppie said:
i want 200505 converted to 200505, the problem is it looks like 20050
but is something else. so when i run a vlookup function to compar
that date with my date i get an inaccurate output b/c excel doesn'
read the 200505 as a date, so when i convert it to a date i get tha
strange return i previously posted.
Click to expand...

I realize that "200505" is representing a date BUT for VLooku
purposes, does it have to be "date" formatted? Why don't you jus
format all your entries that look like "yyyymm" as general
 
Top