Date Format

%3CLINGO-SUB%20id%3D%22lingo-sub-1530797%22%20slang%3D%22en-US%22%3EDate%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1530797%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20present%20Date%20Format%20is%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22170%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22170%22%3E2020-06-17T00%3A00%3A00.000Z%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20it%20as%20%22%20YYYY-MM-DD%22%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20suggest%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1530797%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1530982%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1530982%22%20slang%3D%22en-US%22%3EUse%20%3D%20TEXT(Your%20input%2C%22YYYY-MM-DD%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531274%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531274%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725726%22%20target%3D%22_blank%22%3E%40OwenPrice%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20formula%20returns%20text%2C%20not%20date.%20If%2C%20for%20example%2C%20check%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DB1%26gt%3BDATE(2021%2C1%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eit%20returns%20TRUE%20since%20any%20text%20is%20always%20%22more%22%20than%20any%20number.%20Another%20story%20is%20that%20use%20such%20text%20in%20arithmetic%20operations%20or%20some%20formulas%20Excel%20automatically%20transforms%20text%20to%20number.%20Same%20as%26nbsp%3B%3D%2210%22%2B1%20returns%2011.%3C%2FP%3E%0A%3CP%3EMore%20reliable%20will%20be%20to%20push%20such%20conversion%20as%20%3D--LEFT(A1%2C10)%20and%20apply%20date%20format%20to%20the%20result.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20we%20have%20column%20with%20datetimezone%20texts%20we%20may%20apply%20to%20it%20Date-%26gt%3BText%20to%20Columns%20using%20%3CSTRONG%3ET%3C%2FSTRONG%3E%20as%20separator%20and%20remove%20second%20column%20after%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1530921%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1530921%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F732571%22%20target%3D%22_blank%22%3E%40Yugan475%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESuppose%20your%20date%20is%20in%20cell%20A1%2C%20use%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLEFT(A1%2C10)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%2C%20this%20simply%20takes%20the%20first%2010%20characters%20from%20your%20current%20timestamp%20string%20and%20Excel%20properly%20recognizes%20it%20as%20a%20date%2C%20on%20which%20you%20can%20use%20Date-based%20functions%20and%20date%20arithmetic%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22OwenPrice_0-1595080355862.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206235i6560C47E301A8242%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22OwenPrice_0-1595080355862.png%22%20alt%3D%22OwenPrice_0-1595080355862.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEDIT%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20useful%20feedback%20from%20%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%20regarding%20the%20above%20recommendation.%3CBR%20%2F%3E%3CBR%20%2F%3EWhile%20Excel%20acting%20on%20the%20above%20formula%20and%20arithmetic%20will%20%3CEM%3Erecognize%20%3C%2FEM%3Ethe%20result%20as%20a%20date%2C%20it%20is%20not%20foolproof%2C%20so%20wrap%20the%20LEFT%20function%20with%20DATEVALUE%20to%20force%20the%20result%20into%20the%20correct%20underlying%20format.%20Then%20format%20the%20cell%20as%20a%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DDATEVALUE(LEFT(A1%2C10))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20prefer%20DATEVALUE%20to%20the%20--%20operator%20as%20what's%20happening%20is%20more%20clear%20to%20readers.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

The present Date Format is 

2020-06-17T00:00:00.000Z

 

I want it as " YYYY-MM-DD" 

Please suggest

3 Replies

@Yugan475 

 

Suppose your date is in cell A1, use this:

 

 

=LEFT(A1,10)

 

 

As you can see, this simply takes the first 10 characters from your current timestamp string and Excel properly recognizes it as a date, on which you can use Date-based functions and date arithmetic:

 

OwenPrice_0-1595080355862.png

 

EDIT:

 

Some useful feedback from @Sergei Baklan regarding the above recommendation.

While Excel acting on the above formula and arithmetic will recognize the result as a date, it is not foolproof, so wrap the LEFT function with DATEVALUE to force the result into the correct underlying format. Then format the cell as a date.

 

 

=DATEVALUE(LEFT(A1,10))

 


I prefer DATEVALUE to the -- operator as what's happening is more clear to readers.

Use = TEXT(Your input,"YYYY-MM-DD")

@OwenPrice 

This formula returns text, not date. If, for example, check

=B1>DATE(2021,1,1)

it returns TRUE since any text is always "more" than any number. Another story is that use such text in arithmetic operations or some formulas Excel automatically transforms text to number. Same as ="10"+1 returns 11.

More reliable will be to push such conversion as =--LEFT(A1,10) and apply date format to the result.

 

If we have column with datetimezone texts we may apply to it Date->Text to Columns using T as separator and remove second column after that.