New-ConditionalText and formating a range with a date less than or between or greater than

[KevinWGagel]

Can New-ConditionalText format a range with a date less than or between or greater than? I can't find a way to work with conditionaltext to format a column based on the datetime value within the column.

Here are examples of what I've tried:
$format = New-ConditionalText -ConditionalType LessThanorEqual -Text Today Black Red -Range "G:G"
Formats the whole column including the header.
$format = New-ConditionalText -ConditionalType ContainsText -Text '2015' Black Red -Range "G:G"
Nothing gets formatted in the column.
$format = New-ConditionalText -ConditionalType LessThanOrEqual -Text $(Get-Date -Format 'yyyy/MM/dd') Black Red -Range "G:G"
All values in the column get formatted but the header is left alone.
$format = New-ConditionalText -ConditionalType LessThanOrEqual -Text $(Get-Date -Format 'yyyy/MM/dd h:mm') Black Red -Range "G:G"
Same result as the one before.

Here is some sample data that I'm trying to color code:
10/13/2026 0:00 10/9/2018 0:00 10/9/2018 0:00 10/9/2018 0:00 1/9/2024 0:00 1/13/2015 0:00 1/9/2024 0:00 1/9/2024 0:00 1/11/2022 0:00 1/11/2022 0:00 1/9/2024 0:00
The goal is to color code dates prior to today with red background, dates that are today up to a year in the future with a yellow background and dates beyond that with a green background.

I've spent the whole day trying to find a way to make this work, so I don't have to edit the spreadsheet to add formatting. Any assistance is appreciated.

[dfinke]

Thanks for using ImportExcel.

Don't know. Can you do it manually? Can you post an xlsx file that works the way you want?

We can unzip it and inspect the XML to see what works.

[KevinWGagel]

Thank you for your response. I've had some difficulty making a formula in Excel to do what I want so I ended up using three rules to do the highlighting I'm after. I've deleted the columns that contain sensitive information. The attached spreadsheet should demonstrate what I'm trying to do.

EditedVersion01.xlsx

The difficulty with Excel is that a cell formatted as date is treated as a serial number. Office365 (at least ours) will not compare dates using greater or less than for a date (ie is cell greater than TODAY() etc). To make the sheet work, I had to apply the format to a single cell, specifying the date I want to compare to, saving it and then inspect the serial number it is comparing against. Then re-work the rule to use the serial number in the formula - then it worked for the whole column.

It would be nice to be able to use ImportExcel's conditional formatting to format a range where a date is greater/less or between some date because Excel's internal representation of a date is not necessarily known.

Regardless. Thank you for looking at this.

[dfinke]

Yup, date formats, they are fun :-)

ImportExcel uses pretty much the same object model that is in the Excel application.
Sounds like you'd need to mimic what you did manually in code. Add or setup up a new cell and do all that work using PowerShell.

Don't have anything that can do that in the module.

You'd need to export-excel, use passthru to get to the object model and then use the underlying conditional formatting functions.

Looks like you are doing that already?

Not a scenario I have worked thru in either Excel directly or using the module.

Let me know how it goes, post as you go if you want. If anything comes to mind, I will comment.

[KevinWGagel]

I'll let you know how it goes. Thank you for your time.

[JuanMNaranjo]

HEllo : I hope thiis helps
$Offset_Date = (Get-Date $Last_Week_Start -Format 'dd/MM/yyyy h:mm')
$ct1 = New-ConditionalText -Range "WhenCreated" -ConditionalType LessThan -Text $Offset_Date -ConditionalTextColor White -BackgroundColor Green
$ct1 = New-ConditionalText -Range "WhenCreated" -ConditionalType GreaterThanOrEqual -Text $Offset_Date -ConditionalTextColor White -BackgroundColor Red