|
Number Conversion
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. -- badpuppie ------------------------------------------------------------------------ badpuppie's Profile: http://www.excelforum.com/member.php...o&userid=15453 View this thread: http://www.excelforum.com/showthread...hreadid=375721 |
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 "badpuppie" wrote in message ... 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. -- badpuppie ------------------------------------------------------------------------ badpuppie's Profile: http://www.excelforum.com/member.php...o&userid=15453 View this thread: http://www.excelforum.com/showthread...hreadid=375721 |
Can anyone assist? -- badpuppie ------------------------------------------------------------------------ badpuppie's Profile: http://www.excelforum.com/member.php...o&userid=15453 View this thread: http://www.excelforum.com/showthread...hreadid=375721 |
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 I can't seem to convert it accordingly. -- badpuppie ------------------------------------------------------------------------ badpuppie's Profile: http://www.excelforum.com/member.php...o&userid=15453 View this thread: http://www.excelforum.com/showthread...hreadid=375721 |
If your cell has 200505, how do you want it to be converted? Do you want it converted to *May 2005?* -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=375721 |
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.... -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=375721 |
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. -- badpuppie ------------------------------------------------------------------------ badpuppie's Profile: http://www.excelforum.com/member.php...o&userid=15453 View this thread: http://www.excelforum.com/showthread...hreadid=375721 |
badpuppie Wrote: 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. I realize that "200505" is representing a date BUT for VLookup purposes, does it have to be "date" formatted? Why don't you just format all your entries that look like "yyyymm" as general? -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=375721 |
| All times are GMT +1. The time now is 12:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com