This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I've run into this issue. I have many files where the date that I need access to is formatted as a number in Excel. I did some research on the number and found that this number is the number of days that had passed since 1/1/1900.
With this information at the ready, I created a calculation that loked at the date to determine if it was a number. If it was a number, then it did a DateTimeAdd Function of 1/1/1900 + the number formatted date field from excel. Otherwise it just returned the date.
I quickly realized that almost all of my date fields were exactly 3 days off. So, I subtracted 3 days from the number formatted date field. This causes the date to be correct for 98% of my files. However, every so oftern(Particularly a file that should have had the date 12/1 and another that should have had the date 12/7 require that the subtraction be only 2 days, rather than 3.
If I can identify the cause, I could at least build an IF function for it, but even that makes me nervous unless I know the root issue. Obviously writing an excel macro to fix the date formats is possible, but I really would like it to be my last resort incase I run into this issue again.
What year are these dates in? Can you provide an excel file with the incorrect dates?
@Ned has given an explanation here about where the errors in Excel date formatting come from on dates in early 1900. The difference of 3 will most likely be, 1 day for Excel thinking 1900 was a leap year, 1 day for 1900-01-00 incorrect date in Excel, and 1 day because 1/1/1900 is being double counted.
I can't say for sure without seeing the data, but the above should give you an idea as to where the issue may lie. There is some other information on the issue at the Microsoft KB.
I'm looking at 2015 dates. Problem is when an Excel Date is formatted as a number, it comes up as the number of days since 1900, or so they say. So I'm creating a formula that takes 1/1/1900 and adds the number of days in the Excel Date column. These other issues cause it to be a few days off and I'm running into specific situations where some days are off 3 and some are only off 2, which is making my adjustment quite hard.
I need to look over and reply to the other poster once I get some time to mock up an example.
Thanks for the suggestion. Unfortunatley, the point of this was that I had a file for each day, dating back to last year, that I was loading into Alteryx using the Wildcard macro to create a historical database. If I wanted to open each file and do something, I would just change them to a date format, the point was to find a solution in Alteryx so I could avoid that.
I ended up just pulling in my Excel database I had been maintaining instead of recreating the database from all of the files. if I could have recreated, I would have been able to provide more segmented info than the previous report was capable of, but being able to keep what I already have is certainly a start.