Forum Discussion

TES10's avatar
TES10
Copper Contributor
Apr 11, 2019

Change date formula

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

 

  

Formulas and Functions

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 11, 2019

    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?

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 11, 2019

        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.

Resources