Date conversion from Text to DATEVALUE accepted value

%3CLINGO-SUB%20id%3D%22lingo-sub-2077548%22%20slang%3D%22en-US%22%3EDate%20conversion%20from%20Text%20to%20DATEVALUE%20accepted%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2077548%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20my%20downloaded%20Excel%20sheet%2C%20the%20date%20value%20is%20in%20MM%2FDD%2FCCYY%20format%20but%20my%20Laptop%20date%20format%20is%20in%20DD%2FMM%2FCCYY.%20So%20the%20value%20that%20has%20come%20as%20January%2010th%20is%20read%20as%20Oct%201st.%20How%20can%20I%20read%20the%20date%20as%20it%20was%20intended%20%3F%3C%2FP%3E%3CP%3EAlso%20DATEVALUE%20function%20is%20expecting%20the%20Date%20in%20my%20Laptop%20Date%20format%20(%20like%20DD%2FMM%2FCCYY%20)%20so%20it%20throws%20an%20error%20(%20%23VALUE!%20)%20when%20a%20date%20has%20come%20as%2001%2F19%2F2021.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2077548%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-2077929%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20conversion%20from%20Text%20to%20DATEVALUE%20accepted%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2077929%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20extension%20does%20the%20downloaded%20Excel%20sheet%20have%3F%20Is%20it%20.xls%20or%20.xlsx%3F%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20.xlsx%20then%20best%20option%20would%20likely%20be%20to%20use%20Power%20Query%20to%20pull%20in%20the%20data%20from%20the%20unopened%20file.%20%3CBR%20%2F%3E%3CBR%20%2F%3EHave%20you%20used%20Power%20Query%20before%3F%20%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20version%20of%20Excel%20are%20you%20using%20e.g.%20Excel%20365%2C%202019%2C%202016%20etc%3C%2FLINGO-BODY%3E
Occasional Visitor

In my downloaded Excel sheet, the date value is in MM/DD/CCYY format but my Laptop date format is in DD/MM/CCYY. So the value that has come as January 10th is read as Oct 1st. How can I read the date as it was intended ?

Also DATEVALUE function is expecting the Date in my Laptop Date format ( like DD/MM/CCYY ) so it throws an error ( #VALUE! ) when a date has come as 01/19/2021.

1 Reply
Hi

What extension does the downloaded Excel sheet have? Is it .xls or .xlsx?

If .xlsx then best option would likely be to use Power Query to pull in the data from the unopened file.

Have you used Power Query before?

What version of Excel are you using e.g. Excel 365, 2019, 2016 etc