How to add conditional formatting on date less than with Epplus

Question

I need to set a conditional formatting based on dates values using Epplus (latest version 4.1.0).

I set my range

var rng= new ExcelAddress(<startingRow>, <startingcolumn>,<endingRow>, <endingColumn>);

the cells in range are formatted as dates. I need to apply conditional formatting to dates less than or equal today

I can do

var format1 = ws.ConditionalFormatting.AddToday(rng);
format1.Style.Fill.BackgroundColor.Color = Color.LightGreen;

and similar formatting with AddLastWeek and AddLast7Days.

But this is not a complete solution and other predefined Excel date conditions do not help a lot.

I would need

var format1 = ws.ConditionalFormatting.AddLessThan(rng).Formula;
format1.Formula = ???

I tried to put in formula several strings containing formatted dates but they are ignored (no error, and no formatting). In Excel I can see them in the manage conditional formatting form. I discovered that if I do

format1.Formula = "A1"

and cell A1 contains "today" formatted as date it works, but I would prefer a solution not involving dummy cells because later I will need more criteria.

Can you suggest me the proper way to solve this problem?

Answer 1

Just have to do the date conversion yourself since EPPlus doesnt have a built in function:

var ltecf = rng.ConditionalFormatting.AddLessThanOrEqual();
ltecf.Formula = DateTime.Now.Date.ToOADate().ToString(CultureInfo.InvariantCulture);
ltecf.Style.Fill.BackgroundColor.Color = Color.LightGreen;