I have a Powershell script that uses this Powershell module. It basically allows me to query Excel workbooks like a database. Everything looks good except it reads dates in as that wonky 5-digit date representation instead of a date.
I’ve checked the column formatting in the files themselves and they are formatted as MM/DD/YYYY as they should be; my script just reads them in as the 5-digit date.
Example: 2/1/10 => 40057
I need to convert 40057 to the appropriate date, either using something native to Powershell (.NET), or even a forumla would set me right.
The number you get, is the number of days since 31/12/1899.
You can convert it by adding the number to the .Net DateTime using AddDays, or you can use DateTime.FromOADate.
Sorry, I don’t know the specific Powershell syntax.
There is something strange with these days in the first two months of 1900.