Powershell imported date and time from Excel column are imported with wrong format

Question

I´m importing a Excel document to a powershell script using Import-Excel module. The import works great but the column with date gets formatted really weird. The date and time in the Excel sheet are formatted like this: yyyy-mm-dd hh:mm (2020-09-01 04:03) but the data that is imported looks like this: 43965,1672916667.

I've tried to add [DateTime] to the variable like this: "Senast ansluten" = [DateTime]$ExcelLok.'Senast ansluten' but then I just get error Cannot convert value "44075.3451851852" to type "System.DateTime". Error: "String was not recognized as a valid DateTime."

How can I specify the format so that it gets read correctly?

$ImportExcel = Import-Excel -Path 'C:\Temp\Powershell scripts\Test\PingFastaIP\Fasta IP-nummer.xlsm' -WorksheetName ADM_UTB

ForEach ($ExcelLok in $ImportExcel){
[PSCustomObject]@{
        "IP address" = $ExcelLok.IP
        "Lokation" = $ExcelLok.Lok
        "Ping status" = $ExcelLok.'Ping status'
        "Senast ansluten" = [DateTime]$ExcelLok.'Senast ansluten'
} | Format-Table -Property `
        @{Name='Lokation';Expression={ $ExcelLok.Lok };align='left';width=15},
        @{Name='IP address';Expression={ $ExcelLok.IP };align='left';width=15},
        @{Name='Ping status';Expression={ $ExcelLok.'Ping status' };align='left';width=20},
        @{Name='Senast ansluten';Expression={ $ExcelLok.'Senast ansluten' }} 
        
}

Have a look at the answers for https://stackoverflow.com/questions/8068353/powershell-reading-excel-date-as-5-digit-number as it sounds more or less like the same question... — mclayton, Sep 15 '20 at 09:59
I failed to find that answer during my searches, many thanks! — Markus Sacramento, Sep 15 '20 at 10:20

score 2 · Answer 1 · answered Jun 10 '21 at 22:19

2

Try this: $ImportExcel = Import-Excel -Path 'C:\Temp\Powershell scripts\Test\PingFastaIP\Fasta IP-nummer.xlsm' -WorksheetName ADM_UTB -AsDate 'DateColumn'

answered Jun 10 '21 at 22:19

Andy

75
1
10

score 1 · Answer 2 · answered Sep 15 '20 at 10:22

The solution became this, thanks to mclayton:

$ImportExcel = Import-Excel -Path 'C:\Temp\Powershell scripts\Test\PingFastaIP\Fasta IP-nummer.xlsm' -WorksheetName ADM_UTB

ForEach ($ExcelLok in $ImportExcel){
[PSCustomObject]@{
        "IP address" = $ExcelLok.IP
        "Lokation" = $ExcelLok.Lok
        "Ping status" = $ExcelLok.'Ping status'
        "Senast ansluten" = [DateTime]::FromOADate($ExcelLok.'Senast ansluten')
} | Format-Table -Property `
        @{Name='Lokation';Expression={ $ExcelLok.Lok };align='left';width=15},
        @{Name='IP address';Expression={ $ExcelLok.IP };align='left';width=15},
        @{Name='Ping status';Expression={ $ExcelLok.'Ping status' };align='left';width=20},
        @{Name='Senast ansluten';Expression={ [DateTime]::FromOADate($ExcelLok.'Senast ansluten') }} 
        
}

Powershell imported date and time from Excel column are imported with wrong format

2 Answers2