import-excel - How to deal with excel datetime format

[jefcoq]

Hello ,
when I use this
Import-Excel 'D:\EP.xlsx' -WorksheetName "Page 1" -ImportColumns @(1)
(column 1 is a date excel format like "18/08/2022 07:14:18"

Unfortunately, in powershell output, I have the float excel format like 43111.3021990741...
Is it possible to format the date the good way (for example yyyy-MM-dd hh:mm) ?
Thanks !

[dfinke]

Please could you show me the data you are trying to work with and simple little script. Are you trying to write that from PowerShell to Excel?

[danubie]

I do have a similar problem with datetime & timespan cells having a "running hour" [h]:mm.
There is a function to convert from "OLE Automation date" (see MS docs). The only thing I recognized is a off-by-one to Excels double representation (at least on my system: Windows10 german, Office in english).
This function works for me to recover datetime from type double:

function ConvertTo-DateTimeFromExcelFloat {
    [CmdletBinding()]
    param (
        [double] $Value
    )
    ([DateTime]::FromOADate($Value)).AddDays(1)
}

In Excel it looks like this

Retured doubles after Import-Excel (german style):

Converting it back from double to datetime:

ConvertTo-DateTimeFromExcelFloat -Value 1.0430902777777777
ConvertTo-DateTimeFromExcelFloat -Value 2.08546296296296
ConvertTo-DateTimeFromExcelFloat -Value 3.12783559027778
ConvertTo-DateTimeFromExcelFloat -Value 4.17020827546296
# output (german style)
Montag, 1. Jänner 1900 01:02:03
Dienstag, 2. Jänner 1900 02:03:04
Mittwoch, 3. Jänner 1900 03:04:04
Donnerstag, 4. Jänner 1900 04:05:0

[danubie]

The last days I already was investigating this a bit deaper

• Both timespan and datetime types do have a similar issue (Export-Excel - Add parameter to set custom formats for datetime and timestamp columns #1429).
• Exporting these types seem to work as expected
• Both get internally represented as doubles
• The DLL returns a datetime object for Id 22 but a double for 164 (internal ids for dateformats)

According to EEPlus WIKI (and as far as I see in code) it is documented that "[h]:mm:ss" and "m/d/yy h:mm" are types they are officially supporting.

I found two workarounds (BTW: both do have the drawback, that you need to know which cells contain datetime or timespan values):

Workaround 1: Use a function to convert the doubles to datetime

function ConvertTo-DateTimeFromExcelFloat {
    [CmdletBinding()]
    param (
        [double] $Value
    )
    ([DateTime]::FromOADate($Value)).AddDays(1)
}

Workaround 2: Set datetime formats to supported values using a temp.copy of the file (longer sample)
The original Excel content looks like this:

Code to demonstrate the workaround:

$Path = '.\OriginalExcel-hm.xlsx'
$PathToModified = '.\OriginalExcel-hm-modified.xlsx'

$result = Import-Excel -Path $Path
Write-Host "Before modifying (format [h]:mm)"
Write-Host ("Import: ['A1'] Value={0}, Type={1}" -f $result.DateTime1, $result.DateTime1.gettype())
Write-Host ("Import: ['B1'] Value={0}, Type={1}" -f $result.RunningTime1, $result.RunningTime1.gettype())
Write-Host ("Import: ['C1'] Value={0}, Type={1}" -f $result.DateTime25, $result.DateTime25.gettype())
Write-Host ("Import: ['D1'] Value={0}, Type={1}" -f $result.RunningTime25, $result.RunningTime25.gettype())
$excel = Open-ExcelPackage -Path $Path
$ws = $excel.Sheet1

Write-Host "Results from Open-ExcelPackage"
Write-Host ("OpenEP: ['A1'] Value={0}, Text={1}, Format={2}" -f $ws.Cells['A2'].Value, $ws.Cells['A2'].Text, $ws.Cells['A2'].Style.Numberformat.Format)
Write-Host ("OpenEP: ['B1'] Value={0}, Text={1}, Format={2}" -f $ws.Cells['B2'].Value, $ws.Cells['B2'].Text, $ws.Cells['B2'].Style.Numberformat.Format)
Write-Host ("OpenEP: ['C1'] Value={0}, Text={1}, Format={2}" -f $ws.Cells['C2'].Value, $ws.Cells['C2'].Text, $ws.Cells['C2'].Style.Numberformat.Format)
Write-Host ("OpenEP: ['D1'] Value={0}, Text={1}, Format={2}" -f $ws.Cells['D2'].Value, $ws.Cells['D2'].Text, $ws.Cells['D2'].Style.Numberformat.Format)

Write-Host "Now modifying formats"

$ws.Cells['A2'].Style.Numberformat.Format = 'm/d/yy h:mm'
$ws.Cells['B2'].Style.Numberformat.Format = '[h]:mm:ss'
$ws.Cells['C2'].Style.Numberformat.Format = 'm/d/yy h:mm'
$ws.Cells['D2'].Style.Numberformat.Format = '[h]:mm:ss'

$excel.SaveAs($PathToModified)
Close-ExcelPackage $excel -NoSave

$result = Import-Excel -Path $PathToModified
Write-Host "After modifying dateformat"
Write-Host ("Import: ['A1'] Value={0}, Type={1}" -f $result.DateTime1, $result.DateTime1.gettype())
Write-Host ("Import: ['B1'] Value={0}, Type={1}" -f $result.RunningTime1, $result.RunningTime1.gettype())
Write-Host ("Import: ['C1'] Value={0}, Type={1}" -f $result.DateTime25, $result.DateTime25.gettype())
Write-Host ("Import: ['D1'] Value={0}, Type={1}" -f $result.RunningTime25, $result.RunningTime25.gettype())

$excel = Open-ExcelPackage -Path $PathToModified
$ws = $excel.Sheet1
Write-Host "Results from Open-ExcelPackage"
Write-Host ("OpenEP: ['A1'] Value={0}, Text={1}, Format={2}" -f $ws.Cells['A2'].Value, $ws.Cells['A2'].Text, $ws.Cells['A2'].Style.Numberformat.Format)
Write-Host ("OpenEP: ['B1'] Value={0}, Text={1}, Format={2}" -f $ws.Cells['B2'].Value, $ws.Cells['B2'].Text, $ws.Cells['B2'].Style.Numberformat.Format)
Write-Host ("OpenEP: ['C1'] Value={0}, Text={1}, Format={2}" -f $ws.Cells['C2'].Value, $ws.Cells['C2'].Text, $ws.Cells['C2'].Style.Numberformat.Format)
Write-Host ("OpenEP: ['D1'] Value={0}, Text={1}, Format={2}" -f $ws.Cells['D2'].Value, $ws.Cells['D2'].Text, $ws.Cells['D2'].Style.Numberformat.Format)

Output:\

Before modifying (format [h]:mm)
Import: ['A1'] Value=31.12.1899 01:02:03, Type=System.DateTime
Import: ['B1'] Value=0,04305555555555556, Type=System.Double
Import: ['C1'] Value=1,0430902777777777, Type=System.Double
Import: ['D1'] Value=1,0430902777777777, Type=System.Double
Results from Open-ExcelPackage
OpenEP: ['A1'] Value=31.12.1899 01:02:03, Text=12.31.99 1:02, Format=m/d/yy h:mm
OpenEP: ['B1'] Value=0,04305555555555556, Text=1:12, Format=[h]:mm
OpenEP: ['C1'] Value=1,0430902777777777, Text=31.12.1899 01:02:03, Format=dd/mm/yyyy\ hh:mm:ss
OpenEP: ['D1'] Value=1,0430902777777777, Text=1:12, Format=[h]:mm
Now modifying formats
After modifying dateformat
Import: ['A1'] Value=31.12.1899 01:02:03, Type=System.DateTime
Import: ['B1'] Value=30.12.1899 01:02:00, Type=System.DateTime
Import: ['C1'] Value=31.12.1899 01:02:03, Type=System.DateTime
Import: ['D1'] Value=31.12.1899 01:02:03, Type=System.DateTime
Results from Open-ExcelPackage
OpenEP: ['A1'] Value=31.12.1899 01:02:03, Text=12.31.99 1:02, Format=m/d/yy h:mm
OpenEP: ['B1'] Value=30.12.1899 01:02:00, Text=1:12:00, Format=[h]:mm:ss
OpenEP: ['C1'] Value=31.12.1899 01:02:03, Text=12.31.99 1:02, Format=m/d/yy h:mm
OpenEP: ['D1'] Value=31.12.1899 01:02:03, Text=1:12:03, Format=[h]:mm:ss