SOLVED

Date Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-1634276%22%20slang%3D%22en-US%22%3EDate%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1634276%22%20slang%3D%22en-US%22%3E%3CP%3EDates%20shorter%20than%20or%20equal%20to%2012%20are%20right%20aligned.......rest%20are%20left%20aligned.%3C%2FP%3E%3CP%3EWhen%20I%20am%20trying%20to%20change%20the%20format%2C%20it%20happens%20only%20for%20right%20aligned(Dates%20shorter%20than%20or%20equal%20to%2012).%20Please%20have%20a%20look%20at%20the%20attached%20file.%20Please%20help%20me%20out%20!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1634276%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1634845%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1634845%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F780709%22%20target%3D%22_blank%22%3E%40Jagdeep_%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20suggest%20select%20the%20column%2C%20Data-%26gt%3BText%20to%20Columns%20and%20on%20third%20step%20of%20the%20wizard%20select%20Date%20and%20MDY%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20680px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216551iFBB37A1E929CC2D3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EResult%20is%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20255px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216552iBF12992BE6081317%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EDo%20that%20column%20by%20column%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Dates shorter than or equal to 12 are right aligned.......rest are left aligned.

When I am trying to change the format, it happens only for right aligned(Dates shorter than or equal to 12). Please have a look at the attached file. Please help me out !!

4 Replies
Highlighted
Best Response confirmed by Jagdeep_ (New Contributor)
Solution

@Jagdeep_ 

I'd suggest select the column, Data->Text to Columns and on third step of the wizard select Date and MDY

image.png

Result is

image.png

Do that column by column

Highlighted

@Sergei Baklan Thank you Sir ! Thanks a lot......It's working. You made it so simple. Sir, I have one more doubt, Our system time format affects our excel date format ?

Highlighted
and now when I am trying to calculate the difference between the dates it's giving me a number. Why so ? Now all the dates are in same format. It should work ?
Highlighted

@Jagdeep_ , you are welcome.

Not sure I understood next question. If you calculate the difference between two dates (like =B1-A1) excel returns the difference in days (how many days are between), that's a number.

If you'd like to have the difference like "4 years 5 months 21 day"  you may use DATEDIF. It is described with the sample here https://support.microsoft.com/en-us/office/datedif-function-25dba1a4-2812-480b-84dd-8b32a451b35c