Apr 11 2019 01:03 AM
Hi
I am trying to make a VLOOKUP on dates, but no matter what I do, and how I format, the funktion in one og the sheets is wrong (see attachment). How do I change the formula line into YYYY-MM-DD?
If I turn it into text, it returns the date code. If I write it all as text I cannot fill series of dates (I have several years).
HELP
Apr 11 2019 01:29 AM
@TES10 , dates in Excel are actually integers numbers starting from 1 which is Jan 01, 1900. It doesn't matter how do you format them, that's only more friendly representation. If you use date as text you may convert it to date/number using DATEVALUE. That's always better not to hardcode date within the formula but keep it in the cell as parameter.
And how your VLOOKUP formula looks like?
Apr 11 2019 02:00 AM - edited Apr 11 2019 02:22 AM
My problem is, that they look differently in the two sheets, and that makes the VLOOKUP impossible.
I have tried to attach the sheet
The formula looks lige this =LOPSLAG(A2;'Ark1'!$A$2:$B$637;2;FALSK)
Apr 11 2019 02:29 AM
@TES10 , your dates in column A are actually texts. Even if you apply date format to that column it doesn't convert texts to dates. You may use DATEVALUE in your formula as
=VLOOKUP(DATEVALUE(A2),'Ark1'!$A$2:$B$637,2,FALSE)
if your default date format is YYYY-MM-DD, or better transform entire column A to dates using Data->Text to Columns and on third step of the wizard selecting
See Sheet 2 in attached with such conversion.
Apr 11 2019 03:19 AM
Apr 11 2019 03:37 AM
@TES10 , first they are aligned left what is default for texts. More reliable is to check with ISTEXT or ISNUMBER