Export-Excel from PowerShell doesnt format dates properly

I've got some data in a CSV file that I'm trying to convert to an XLSX file. I've been trying this using the Import-Excel PS module. Here is an extract of what the CSV file looks like:

AMOUNT_BOUGHT,AREA,INTERNAL_TRAN_NUMBER,VALUE_DATE
56635800,IFU,1052797971,15/05/2025
322526.16,IFU,000001053676071,15/05/2025
432900,IFU,'105747143000001,15/05/2025
20895626,IFU,105889514000001,15/05/2025
20544514,IFU,105960931000001,15/05/2025
139100,IFU,105960947000001,15/05/2025

These are the steps I've taken so far:

• Import-Csv to get the data from the CSV file into a PSCustomObject. This imports all of the data as type STRING.

• Use the following command to export the data to Excel:

  $data | Export-Excel -Path .\test1.xlsx
  

This all works pretty well, HOWEVER the issue I'm facing is regarding that final column: VALUE_DATE. When I run the export, the dates in this column are exported in General (text) format, not Date format. I've tried multiple things but I'm unable to get the date into Excel in a date format. One of the things I tried was the following:

foreach ($row in $data) {
    $row.VALUE_DATE = [datetime]::ParseExact($row.VALUE_DATE, 'dd/MM/yyyy', $null)
}

$data | Export-Excel -Path .\test2.xlsx

This comes pretty close to what I'm looking for, BUT the values in the Excel file are now in Custom format (still not Date) and they come with the date AND TIME whereas I want it to show just the date.

Anybody have any ideas on how I can either:

• Format the date column in DATE format
• Format the date column in CUSTOM format but with type dd/mm/yyyy, NOT dd/mm/yyyy hh:mm

Any help is appreciated, thanks!

You should be able to use Set-ExcelColumn with the -NumberFormat Parameter, unfortunately I don't have Excel available to test the code. You can test using NumberFormat equal to dd/MM/yyyy as in this example or, you could also try using Short Date though this will localize them.

# a `test.xlsx` file in the current directory
$pkg = Open-ExcelPackage -Path test.xlsx -Create

# parse the csv into objects
$csv = Import-Csv .\test.csv

# convert the strings in `VALUE_DATE` column to DateTime
foreach ($line in $csv) {
    $line.VALUE_DATE = [datetime]::ParseExact(
        $line.VALUE_DATE, 'dd/MM/yyyy',
        [cultureinfo]::InvariantCulture)
}

# export the content to the memory package
$exportExcelSplat = @{
    InputObject   = $csv
    ExcelPackage  = $pkg
    WorksheetName = 'myTestWorkSheet'
    PassThru      = $true
}
$pkg = Export-Excel @exportExcelSplat

# set the 4th column (`date` column in the example) to the desired Date format
$setExcelColumnSplat = @{
    ExcelPackage  = $pkg
    Column        = 4
    # alternatively, test with `NumberFormat  = 'Short Date'`
    NumberFormat  = 'dd/MM/yyyy'
    WorksheetName = 'myTestWorkSheet'
}
Set-ExcelColumn @setExcelColumnSplat

# save the package to disk and open it
Close-ExcelPackage $pkg -Show