Convert Number to date

This topic contains 4 replies, has 3 voices, and was last updated by  Codernator 1 week ago.

  • Author
    Posts
  • #104188

    Codernator
    Participant

    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

  • #104191

    Jack
    Participant

    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

    • #104192

      Codernator
      Participant

      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:\>
      
      
    • #104200

      Mike R.
      Participant

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

      Example:

      [datetime]::FromOADate($datenumber)
    • #104207

      Codernator
      Participant

      Great that worked 🙂

      Thanks Mike

You must be logged in to reply to this topic.