SOLVED

excel report formatting issue

%3CLINGO-SUB%20id%3D%22lingo-sub-3349267%22%20slang%3D%22en-US%22%3Eexcel%20report%20formatting%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3349267%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EWe%20checked%20in%20code%20and%20see%20that%20all%20the%20date%20format%20is%20coming%20properly%20in%20Sql%2C%20but%20when%20the%20same%20data%20is%20generated%20to%20excel%20file%20format%20using%20.net%20code%20%2C%20there%20is%20a%20formatting%20issue.%20this%20issue%20not%20occurring%20in%20excel%202016%26nbsp%3B%20but%20with%20latest%20excel%202021%26nbsp%3B%3C%2FSPAN%3E%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%22Arindam1300_0-1652171100437.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F370424iFA7F1C156B421D2A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Arindam1300_0-1652171100437.png%22%20alt%3D%22Arindam1300_0-1652171100437.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3349267%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3349346%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20report%20formatting%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3349346%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1385653%22%20target%3D%22_blank%22%3E%40Arindam1300%3C%2FA%3E%26nbsp%3BI%20suspect%20that%20your%20system%20is%20set-up%20to%20work%20with%20dates%20in%20the%20dd%2Fmm%2Fyyyy%20format.%20Upon%20import%2C%20a%20date%20like%2005%2F02%2F2022%20is%20recognized%20as%20date%205%20Feb%202022%2C%20where%20you%20probably%20intend%20it%20to%20be%20May%202%202022.%20A%20date%20like%2004%2F21%2F2022%20is%20not%20seen%20as%20a%20valid%20date%20and%20get%20imported%2C%20unchanged%2C%20as%20a%20text.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3350104%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20report%20formatting%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3350104%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%2C%20Thanks%20for%20quick%20response%20%2C%20after%20system%20date%20format%20was%20changed%20it%20is%20displaying%20format%20correctly%20.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

We checked in code and see that all the date format is coming properly in Sql, but when the same data is generated to excel file format using .net code , there is a formatting issue. this issue not occurring in excel 2016  but with latest excel 2021 

 

Arindam1300_0-1652171100437.png

 

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@Arindam1300 I suspect that your system is set-up to work with dates in the dd/mm/yyyy format. Upon import, a date like 05/02/2022 is recognized as date 5 Feb 2022, where you probably intend it to be May 2 2022. A date like 04/21/2022 is not seen as a valid date and get imported, unchanged, as a text.

@Riny_van_Eekelen , Thanks for quick response , after system date format was changed it is displaying format correctly .