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?

Highlighted

@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

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies