Hey all!
I am writing a script that imports data from Excel for tracking installation of caissons. The cell data includes dates input by our field staff. When those dates come into Dynamo they are reading as weird numbers…
For example, the date in the Excel sheet may be 11/03/2017 but in Dynamo is reads “43042” …
Has anyone run into this issue? Any solutions…? Workarounds?
If it isn’t a matter of the “readAsStrings” input, here is a workaround:
d1 = DSCore.DateTime.FromString("1/1/1900");
ts = DSCore.TimeSpan.Create(x - 2, 0, 0, 0, 0);
dt = DSCore.DateTime.AddTimeSpan(d1, ts);
dtout = DSCore.DateTime.Format(dt, "MM/dd/yyyy");Amy, this looks like it will do the trick…! Awesome! How on earth did you decipher the the time stamp formula and convert it to design script…? Brilliant…
Excel uses serial codes for dates so they can be used in calculations, and the serial codes are number of days after 01/01/1900: http://www.cpearson.com/excel/datetime.htm
The “x-2” part is because Excel has a bug inherited from Lotus 1-2-3 which causes it to treat 1900 as a leap year when it is not one: https://support.microsoft.com/en-us/help/214326/excel-incorrectly-assumes-that-the-year-1900-is-a-leap-year
This is the graphical version of what I did, but I wrote it in a code block rather than use nodes because it seemed like too much graph space taken up just to convert values so they read correctly 
I wasn’t able to replicate the problem of dates coming in as the Excel serial codes in Dynamo, but I’m almost certain I have seen this happen before… I am curious as to what causes it 
