Change date formula

Copper Contributor

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

 

  

5 Replies

@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?

@Sergei Baklan 

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)

@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

image.png

See Sheet 2 in attached with such conversion.

@Sergei Baklan 

 

Thank you so much! How do you see that colum A is only text?

@TES10 , first they are aligned left what is default for texts. More reliable is to check with ISTEXT or ISNUMBER

 

image.png