Home

Excel Date Conversion

%3CLINGO-SUB%20id%3D%22lingo-sub-688035%22%20slang%3D%22en-US%22%3EExcel%20Date%20Conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-688035%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20been%20trying%20all%20day%20to%20convert%202019-04-29T00%3A00%3A0004%3A00%20to%20yyyy-MM-dd.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan't%20seem%20to%20figure%20it%20out.%26nbsp%3B%20I%20had%20the%20formula%20once%20before%2C%20years%20ago%2C%20but%20no%20luck%20this%20time.%26nbsp%3B%20So%20frustrating.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-688035%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-688252%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Date%20Conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-688252%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F359109%22%20target%3D%22_blank%22%3E%40scohen819%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20this%20formula%20below%20to%20convert%20it%20into%20a%20proper%20date%3A%3C%2FP%3E%3CPRE%3E%3DDATE(LEFT(A1%2C4)%2CMID(A1%2C6%2C2)%2CMID(A1%2C9%2C2))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20that%20you%20can%20press%20Ctrl%2B1%20to%20open%20the%20Format%20Cells%20dialog%20box%2C%20then%20go%20to%20Custom%20and%20use%20this%20custom%20format%20(yyyy%2Fmm%2Fdd)%2C%20then%20hit%20OK.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117946i3EA6124163545A71%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Custom%20Date%20Format.png%22%20title%3D%22Custom%20Date%20Format.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
scohen819
Occasional Visitor

I've been trying all day to convert 2019-04-29T00:00:0004:00 to yyyy-MM-dd.

 

Can't seem to figure it out.  I had the formula once before, years ago, but no luck this time.  So frustrating.

 

Thanks!!!

 

 

1 Reply

@scohen819 

 

Hi,

 

You can use this formula below to convert it into a proper date:

=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))

 

After that you can press Ctrl+1 to open the Format Cells dialog box, then go to Custom and use this custom format (yyyy/mm/dd), then hit OK.

Custom Date Format.png

 

Hope that helps

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 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
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies