Home

Change date formula

%3CLINGO-SUB%20id%3D%22lingo-sub-434952%22%20slang%3D%22en-US%22%3EChange%20date%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-434952%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20make%20a%20VLOOKUP%20on%20dates%2C%20but%20no%20matter%20what%20I%20do%2C%20and%20how%20I%20format%2C%20the%20funktion%20in%20one%20og%20the%20sheets%20is%20wrong%20(see%20attachment).%20How%20do%20I%20change%20the%20formula%20line%20into%20YYYY-MM-DD%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20turn%20it%20into%20text%2C%20it%20returns%20the%20date%20code.%20If%20I%20write%20it%20all%20as%20text%20I%20cannot%20fill%20series%20of%20dates%20(I%20have%20several%20years).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHELP%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-434952%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-435115%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20date%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-435115%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319157%22%20target%3D%22_blank%22%3E%40TES10%3C%2FA%3E%26nbsp%3B%2C%20dates%20in%20Excel%20are%20actually%20integers%20numbers%20starting%20from%201%20which%20is%20Jan%2001%2C%201900.%20It%20doesn't%20matter%20how%20do%20you%20format%20them%2C%20that's%20only%20more%20friendly%20representation.%20If%20you%20use%20date%20as%20text%20you%20may%20convert%20it%20to%20date%2Fnumber%20using%20DATEVALUE.%20That's%20always%20better%20not%20to%20hardcode%20date%20within%20the%20formula%20but%20keep%20it%20in%20the%20cell%20as%20parameter.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20how%20your%20VLOOKUP%20formula%20looks%20like%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-435298%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20date%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-435298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20is%2C%20that%20they%20look%20differently%20in%20the%20two%20sheets%2C%20and%20that%20makes%20the%20VLOOKUP%20impossible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20to%20attach%20the%20sheet%3C%2FP%3E%3CP%3EThe%20formula%20looks%20lige%20this%26nbsp%3B%3CFONT%3E%3DLOPSLAG(A2%3B'Ark1'!%24A%242%3A%24B%24637%3B2%3BFALSK)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-435434%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20date%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-435434%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319157%22%20target%3D%22_blank%22%3E%40TES10%3C%2FA%3E%26nbsp%3B%2C%20your%20dates%20in%20column%20A%20are%20actually%20texts.%20Even%20if%20you%20apply%20date%20format%20to%20that%20column%20it%20doesn't%20convert%20texts%20to%20dates.%20You%20may%20use%20DATEVALUE%20in%20your%20formula%20as%3C%2FP%3E%0A%3CPRE%3E%3DVLOOKUP(DATEVALUE(A2)%2C'Ark1'!%24A%242%3A%24B%24637%2C2%2CFALSE)%3C%2FPRE%3E%0A%3CP%3Eif%20your%20default%20date%20format%20is%20YYYY-MM-DD%2C%20or%20better%20transform%20entire%20column%20A%20to%20dates%20using%20Data-%26gt%3BText%20to%20Columns%20and%20on%20third%20step%20of%20the%20wizard%20selecting%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20185px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108275i07E47654B6201017%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESee%20Sheet%202%20in%20attached%20with%20such%20conversion.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-435798%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20date%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-435798%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much!%20How%20do%20you%20see%20that%20colum%20A%20is%20only%20text%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-435875%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20date%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-435875%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319157%22%20target%3D%22_blank%22%3E%40TES10%3C%2FA%3E%26nbsp%3B%2C%20first%20they%20are%20aligned%20left%20what%20is%20default%20for%20texts.%20More%20reliable%20is%20to%20check%20with%20ISTEXT%20or%20ISNUMBER%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20511px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108277i22BD68F21441943B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
TES10
New 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