EPPlus two color conditional date format

Question

I have a column with dates and I want to conditionally color any cell that is older that 2 week yellow, and any that is older than 90 days red. I can't figure out how to do that.

Answer 1

Should be able to just add the conditions like any other. You can use the TODAY() function in excel and subtract:

[TestMethod]
public void Conditional_Formatting_Date()
{
    //https://stackoverflow.com/questions/56741642/epplus-two-color-conditional-date-format
    var file = new FileInfo(@"c:\temp\Conditional_Formatting_Date.xlsx");
    if (file.Exists)
        file.Delete();

    //Throw in some data
    var dataTable = new DataTable("tblData");
    dataTable.Columns.AddRange(new[] {
        new DataColumn("Col1", typeof(DateTime)),
        new DataColumn("Col3", typeof(string))
    });

    var rnd = new Random();
    for (var i = 0; i < 100; i++)
    {
        var row = dataTable.NewRow();
        row[0] = DateTime.Now.AddDays(-rnd.Next(1, 100));
        row[1] = $"=TODAY() - A{i +1}";
        dataTable.Rows.Add(row);
    }

    //Create a test file    
    using (var package = new ExcelPackage(file))
    {
        //Make the stylesheet
        var ws = package.Workbook.Worksheets.Add("table");
        var range = ws.Cells[1, 1].LoadFromDataTable(dataTable, false);
        ws.Column(1).Style.Numberformat.Format = "mm-dd-yy";
        ws.Column(1).AutoFit();

        //Add the calc check
        var count = 0;
        foreach (DataRow row in dataTable.Rows)
            ws.Cells[++count, 2].Formula = row[1].ToString();

        //Add the conditions - order matters
        var rangeA = range.Offset(0, 0, count, 1);

        var condition90 = ws.ConditionalFormatting.AddExpression(rangeA);
        condition90.Style.Font.Color.Color = Color.White;
        condition90.Style.Fill.PatternType = ExcelFillStyle.Solid;
        condition90.Style.Fill.BackgroundColor.Color = Color.Red;
        condition90.Formula = "TODAY() - A1> 90";
        condition90.StopIfTrue = true;

        var condition14 = ws.ConditionalFormatting.AddExpression(rangeA);
        condition14.Style.Font.Color.Color = Color.Black;
        condition14.Style.Fill.PatternType = ExcelFillStyle.Solid;
        condition14.Style.Fill.BackgroundColor.Color = Color.Yellow;
        condition14.Formula = "TODAY() - A1> 14";

        package.Save();
    }
}

Which gives this in the output: