csv to excel export-excel with filter option date field conversions

[sammysammyss]

Hello, I'm using the importexcel module to import a csv, parse the data, convert some columns to the datetime format and then export to xlsx.

So far importexcel has worked really well, but i'm a bit stuck on something. I need my date columns to export to excel with a filter option that keeps the data type as date, so it can be filtered by month year etc. I can do this, but the formatting within excel isn't ideal after the export. These are dates that were originally in a dd-MM-yy format in the csv, that I have to convert to [DateTime] in powershell to even get them to show up as a proper date data type when I use export-excel.

I am wondering if there's any way to use export-excel and get these date formats to either remove the hours, seconds and even better to keep them in a dd-MM-yyyy and still count as "dates" within excel.

Below is the member properties for one of the date fields before I export it to excel and a screenshot of what the date looks like in Excel with the filter option sort of highlighted.

I can't simply use the -numberformat 'short date' because there are other number fields in the document and it doesn't really seem to work on these date columns anyway :).

PS C:\csv2xl> $data |Get-Member |where name -like "date"

• original format from csv: 2-Jun-23
• format after doing this conversion to every row of every column that should be a date "$daterow.$datecolumn = [Datetime]$daterow.$datecolumn" : Friday, June 2, 2023 12:00:00 AM
• column and property info below:
  Name MemberType Definition

---

WEY_DATE_WORKED NoteProperty datetime WEY_DATE_WORKED=5/29/2023 12:00:00 AM

• What the export looks like in excel with the not great date formatting and the filter optiosn for date type column. Ideally I would even make the dates look lke they did in the original csv 2-JUN-23, but keep the column as a filterable date type column.

Any help is appreciated. I've looked for similar solutions and haven't found one.

Thank you

Oh yeah, here is my final export of the manipuated csv data/columns just in case it's useful. The $stringcolumns is just a variable identifying all column names that should keep leading zeroes

$data|Export-Excel -AutoSize -NoNumberConversion $stringcolumns -TableStyle Medium13 -path $path'.xlsx'

[sammysammyss]

I did find a way to get the date format to look right and still be date data type in excel that can be filtered. The problem is that it converts all of the "number" columns as well. I can filter out the columns I don't want the number conversion to run on, but it would be a great feature to specify what columns you do want the number format to run on. Something that basically works like the nonumberconversion command. That way the number columns will convert as normal. Something like:

Export-Excel -NoNumberConversion $stringcolumns -Numberformat.ShortDate $datecolumns

[sammysammyss]

I found the solution to my problem. Just posting here for anyone else like me who doesn't fully understand all the importexcel options :)

I generate my file just like normal into an excel format:

$data|Export-Excel -AutoSize -NoNumberConversion $stringcolumns -TableStyle Medium13 -path $path'.xlsx'

Then I use the open-excelpackage to modify the format of my date fields:

$excel = Open-ExcelPackage $path'.xlsx'
$ws = $excel.Workbook.Worksheets[1]
$datecolumnnumbers = $excel.sheet1.tables.columns |where name -like $datecolumns |select -ExpandProperty id

#change date columns to friendly format
foreach ($num in $datecolumnnumbers){
Set-ExcelRange -address $ws.column($num) -numberFormat 'd-mmm-yyyy'
}

#close and save excel file
Close-ExcelPackage $excel