Exporting dates to excel in DATE format

[mrk907]

Hi all,

I have a CSV file that looks like the following:

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
20297883,IFU,105961135000001,15/05/2025
137300,IFU,105961166000001,15/05/2025

This essentially contains 4 columns, but the relevant one is the last one: VALUE_DATE.

I imported this CSV into PS and then used the Export-Excel command to export to XLSX file, however the date doesn't automatically get formatted to date type, it stays as string.

I changed the type within PS from string to datetime. In this case, it kinda works but not fully. It formats the dates to type CUSTOM instead of date (which is not really an issue) but the issue is that it writes it with the date AND TIME, whereas I want just the date.

Here is the code I have used:

$data = Import-Csv -Path .\Generated_CSV.csv

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

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

I have also tried changing the NumberFormat within the Export-Excel command and this also doesnt work (or maybe Im not doing it properly).

Can someone please help with this? Thanks!

[scriptingstudio]

Export-Excel function applies a hardcoded format as m/d/yy h:mm