Convert Number to date

Welcome Forums General PowerShell Q&A Convert Number to date

This topic contains 4 replies, has 3 voices, and was last updated by

 
Participant
3 months, 2 weeks ago.

  • Author
    Posts
  • #104188

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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

      Participant
      Points: 0
      Rank: Member

      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

      Participant
      Points: 1
      Rank: Member

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

      Example:

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

      Participant
      Points: 0
      Rank: Member

      Great that worked 🙂

      Thanks Mike

The topic ‘Convert Number to date’ is closed to new replies.