Convert Number to date

Hi guys,

I’m getting data from excel using importexcel module.
the data that im getting back it’s in array.
one of the value that I’m getting supposed to be a date.
When it comes across in the array it’s showing as number! (I guess because the importexcel module is using property cell.value instead of cell.text)

Right now I have a number 42736 I want to turn it into a date it should be 01/01/2017.
you can type this number in excel then press control shift 3 to change it to date format

how do I convert a number into a date?

Thank you

Hi,

You can cast the number as a datetime type
[datetime] $x

Will you be using the data as a date or is it just for formatting purposes? If it is just formatting you can use the ToString method to format the data as a string

It doesn’t work, I have tried that before.

PS C:\> $datenumber
42736
PS C:\> $datenumber.gettype()

IsPublic IsSerial Name                                     BaseType
-------- -------- ----                                     --------
True     True     Double                                   System.ValueType


PS C:\> [datetime]$datenumber
Cannot convert value "42736" to type "System.DateTime". Error: "String was not recognized as a valid DateTime."
At line:1 char:1
+ [datetime]$datenumber
+ ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvalidCastParseTargetInvocationWithFormatProvider

PS C:\>

Use FromOADate
https://msdn.microsoft.com/en-us/library/system.datetime.fromoadate(v=vs.110).aspx

Example:

[datetime]::FromOADate($datenumber)

Great that worked :slight_smile:

Thanks Mike