SOLVED

Power Query incorrectly loading Date

%3CLINGO-SUB%20id%3D%22lingo-sub-2279223%22%20slang%3D%22en-US%22%3EPower%20Query%20incorrectly%20loading%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2279223%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20Power%20Query%20I'm%20adding%20a%20custom%20column%20and%20then%20changing%20the%20data%20type%20to%20a%20Date.%20The%20output%20correctly%20shows%20the%20day%20and%20years%20in%20Power%20Query%20Editor.%20However%2C%20when%20I%20Close%20%26amp%3B%20Load%2C%20the%20day%20increases%20by%20one%20and%20the%20year%20is%20increased%20by%204.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20why%20this%20is%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20verified%20the%20Locale%20is%20set%20to%20Date%20%26amp%3B%20US.%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%22Joshua_DeFrank_0-1618939359115.png%22%20style%3D%22width%3A%20323px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274073i89A66357FC0D3384%2Fimage-dimensions%2F323x491%3Fv%3Dv2%22%20width%3D%22323%22%20height%3D%22491%22%20role%3D%22button%22%20title%3D%22Joshua_DeFrank_0-1618939359115.png%22%20alt%3D%22Joshua_DeFrank_0-1618939359115.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Joshua_DeFrank_1-1618939384513.png%22%20style%3D%22width%3A%20288px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274074iDD12002809936E86%2Fimage-dimensions%2F288x410%3Fv%3Dv2%22%20width%3D%22288%22%20height%3D%22410%22%20role%3D%22button%22%20title%3D%22Joshua_DeFrank_1-1618939384513.png%22%20alt%3D%22Joshua_DeFrank_1-1618939384513.png%22%20%2F%3E%3C%2FSPAN%3E%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-2279223%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282871%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20incorrectly%20loading%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282871%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B--%20thanks%20that%20was%20exactly%20it!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2279234%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20incorrectly%20loading%20Date%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2279234%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1031733%22%20target%3D%22_blank%22%3E%40Joshua_DeFrank%3C%2FA%3E%26nbsp%3BI%20suspect%20that%20you%20have%20selected%20to%20use%20the%201904%20date%20system.%20Check%20this%20out%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2F1900-and-1904-date-system%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Ftroubleshoot%2Fexcel%2F1900-and-1904-date-system%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

In Power Query I'm adding a custom column and then changing the data type to a Date. The output correctly shows the day and years in Power Query Editor. However, when I Close & Load, the day increases by one and the year is increased by 4.

 

Does anyone know why this is? 

 

I've verified the Locale is set to Date & US.

 

Joshua_DeFrank_0-1618939359115.png

Joshua_DeFrank_1-1618939384513.png

 

 

2 Replies
best response confirmed by Joshua_DeFrank (New Contributor)
Solution

@Joshua_DeFrank I suspect that you have selected to use the 1904 date system. Check this out

https://docs.microsoft.com/en-us/office/troubleshoot/excel/1900-and-1904-date-system 

@Riny_van_Eekelen -- thanks that was exactly it!