Dates changing when exporting a gift detail report to excel

Options
Hi all,


Happy Spring! (she says as it's 50ish with a cold rain.)  I am exporting data from a Gift Detail Report into Excel for our new Controller.  When I open the export file from the "Would you like to open the file now?" prompt the data is correct (Post Date 4/11/2017.)  If I reopen the same saved file later from my Documents folder the post date is 4/11/2021. WHAT?? 


Any ideas, thoughts, explanations? If this has been posted before I apologize for being repetitive.

Comments

  • No solution comes to mind, but thoughts:

    Same version of Excel?

    What Date Format has Excel interpretted from the export file?

    What's the underlying actual data, ie, when you do Ctrl+` to toggle formulae, what shows?

    In the UK, four years is the time span that one can go back with a Gift Aid claim (£1 donation gets 25p extra from HMRC), so perhaps there's some similar financial rule built in RE.

    Try exporting in different file formats, eg, CSV.


    (I'm passionate about controlling dates in Excel, which mostly means I don't accept the default formats. My fav format is DDD DD-MMM-YYYY, because I'm British, but also because with a text month short-name it's unequivocal to other nations - in a previous job I wrote product certification reports on a British product that was exported to America and Canada.)
  • Patti Hommes, bCRE:

    Hi all,


    Happy Spring! (she says as it's 50ish with a cold rain.)  I am exporting data from a Gift Detail Report into Excel for our new Controller.  When I open the export file from the "Would you like to open the file now?" prompt the data is correct (Post Date 4/11/2017.)  If I reopen the same saved file later from my Documents folder the post date is 4/11/2021. WHAT?? 


    Any ideas, thoughts, explanations? If this has been posted before I apologize for being repetitive.

    Excel can be a real pain when it tries to "help" you. Another example is credit card numbers which it will convert to exponentials (eg 2.3456789012E+15) if you're not careful.


    Personally, I'd recommened exporting to csv so that 1) you can see what the native data actually looks like and 2) you can perform an ordered conversion to Excel. By the latter, I mean defining what each column's data type should be manually rather than letting Excel decide. Help is your friend with this if you haven't done it before.


    Hope that helps.


    Steve

Categories