Home

Date format issue

%3CLINGO-SUB%20id%3D%22lingo-sub-753292%22%20slang%3D%22en-US%22%3EDate%20format%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753292%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20have%20date%20in%20Excel%20which%20is%2010%2F7%2F19%20which%20is%20actually%2010th%20of%20July%202019%20but%20when%20i%20convert%20the%20column%20to%20date%20it%20is%20taking%20as%207th%20October%202019%2C%20kindly%20help%20i%20want%20to%20do%20this%20without%20changing%20the%20system%20settings%20as%20it%20is%20locked%20for%20me.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-753292%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753299%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20format%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753299%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375758%22%20target%3D%22_blank%22%3E%40rijuthomas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20Date%20is%20in%20A2%2C%20you%20may%20have%20to%20convert%20it%20to%20this%20date%20in%20B2%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DDATE(YEAR(A2)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EDAY(A2)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EMONTH(A2))%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753367%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20format%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753367%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375758%22%20target%3D%22_blank%22%3E%40rijuthomas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20your%20dates%20are%20in%20one%20column%20another%20variant%20which%20could%20work%20is%20to%20select%20that%20column%2C%20Data-%26gt%3BText%20to%20Columns%20and%20on%20the%20third%20step%20of%20the%20wizard%20select%20Date%20and%20MDY%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766901%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20format%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766901%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3Ethanks%20it%20works%20but%20in%20some%20places%20error%20is%20coming%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-766905%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20format%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-766905%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375758%22%20target%3D%22_blank%22%3E%40rijuthomas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20specify%20what%20errors%20are%20returned%20in%20some%20of%20the%20cells%20where%20the%20formula%20is%20applied%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-833181%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20format%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-833181%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3Esir%20i%20have%20attached%20the%20sample%20sheet%20for%20your%20info%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-833247%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20format%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-833247%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F375758%22%20target%3D%22_blank%22%3E%40rijuthomas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20give%20this%20a%20try...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20B2%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DDATE(RIGHT(A2%2C4)%2CMID(A2%2C4%2C2)%2CLEFT(A2%2C2))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20then%20copy%20it%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
rijuthomas
Occasional Contributor

Hi,

 

i have date in Excel which is 10/7/19 which is actually 10th of July 2019 but when i convert the column to date it is taking as 7th October 2019, kindly help i want to do this without changing the system settings as it is locked for me. 

6 Replies

@rijuthomas 

If your Date is in A2, you may have to convert it to this date in B2: 

=DATE(YEAR(A2),
DAY(A2),
MONTH(A2))

@rijuthomas 

 

If your dates are in one column another variant which could work is to select that column, Data->Text to Columns and on the third step of the wizard select Date and MDY

@Twifoothanks it works but in some places error is coming

@rijuthomas 

Please specify what errors are returned in some of the cells where the formula is applied?

@Twifoosir i have attached the sample sheet for your info

@rijuthomas 

Please give this a try...

 

In B2

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

and then copy it down.

Related Conversations