How to Add Time Periods Conditional Formatting

Possible Usage Scenarios

Using Time Periods conditional formatting in Excel is super useful when working with dates—it helps you visually track and manage time-based data quickly.

1. Instant Insights on Time-Based Data: Quickly highlight things like Today’s tasks, Last month’s sales, Upcoming deadlines, Next week’s appointments.

2. Better Time Management: Helps you stay on top of due dates, events, or expiring items. Great for project timelines, invoices, or schedules.

3. Automatic Updates: It updates dynamically. If today’s date changes, Excel will update the formatting without you lifting a finger.

4. Visual Clarity: Makes time-sensitive information stand out using colors or bold styles — so it doesn’t get missed.

How to Add Time Periods Conditional Formatting Using Excel

Here’s how you can add Time Periods conditional formatting in Excel — super useful for highlighting dates like today, last week, next month, etc.

Steps to Add Time Periods Conditional Formatting:

1. Select the range of date cells you want to format. Example: A2:A50.
2. Go to the Home tab on the ribbon.
3. Click on Conditional Formatting in the Styles group.
4. Hover over Highlight Cells Rules.
5. Click on A Date Occurring…
6. In the dialog box that appears: Use the drop-down to select a time period(Today, Yesterday, Tomorrow, Last 7 days, Last week, Next month, etc.).
7. Choose the format (default is light red fill with dark red text, or click Custom Format to choose your own).
8. Click OK.

How to Add Time Periods Conditional Formatting Using Aspose.Cells for .NET

Aspose.Cells fully supports the conditional formatting provided by Microsoft Excel 2007 and later versions in XLSX format on cells at runtime. This example demonstrates an exercise for Time Periods conditional formatting with different sets of attributes.

private void TestTimePeriod()
{
    // Instantiate a workbook object
    Workbook book = new Workbook();
    // Create a worksheet object and get the first worksheet
    Worksheet _sheet = book.Worksheets[0];

    AddTimePeriod_1(_sheet);
    AddTimePeriod_2(_sheet);
    AddTimePeriod_3(_sheet);
    AddTimePeriod_4(_sheet);
    AddTimePeriod_5(_sheet);
    AddTimePeriod_6(_sheet);
    AddTimePeriod_7(_sheet);
    AddTimePeriod_8(_sheet);
    AddTimePeriod_9(_sheet);
    AddTimePeriod_10(_sheet);

    book.Save(filePath + "TimePeriodType.xlsx");
}

// This method implements the TimePeriod conditional formatting type with Yesterday attribute.
private void AddTimePeriod_10(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I19:K20", Color.MediumSeaGreen, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.Yesterday;
    Cell c = _sheet.Cells["I19"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/07/30"));
    c = _sheet.Cells["K20"];
    c.PutValue(DateTime.Parse("2008/08/03"));

    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I20"];
    c.PutValue("Yesterday");
}
// This method implements the TimePeriod conditional formatting type with Tomorrow attribute.
private void AddTimePeriod_9(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I17:K18", Color.MediumPurple, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.Tomorrow;
    Cell c = _sheet.Cells["I17"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/08/01"));
    c = _sheet.Cells["K18"];
    c.PutValue(DateTime.Parse("2008/08/03"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I18"];
    c.PutValue("Tomorrow");

}
// This method implements the TimePeriod conditional formatting type with ThisWeek attribute.
private void AddTimePeriod_8(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I15:K16", Color.MediumOrchid, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.ThisWeek;
    Cell c = _sheet.Cells["I15"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/07/28"));
    c = _sheet.Cells["K16"];
    c.PutValue(DateTime.Parse("2008/08/03"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I16"];
    c.PutValue("ThisWeek");
}
// This method implements the TimePeriod conditional formatting type with ThisMonth attribute.
private void AddTimePeriod_7(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I13:K14", Color.MediumBlue, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.ThisMonth;
    Cell c = _sheet.Cells["I13"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/07/5"));
    c = _sheet.Cells["K14"];
    c.PutValue(DateTime.Parse("2008/05/30"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I14"];
    c.PutValue("ThisMonth");
}

// This method implements the TimePeriod conditional formatting type with NextWeek attribute.
private void AddTimePeriod_6(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I11:K12", Color.MediumAquamarine, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.NextWeek;
    Cell c = _sheet.Cells["I11"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/08/5"));
    c = _sheet.Cells["K12"];
    c.PutValue(DateTime.Parse("2008/07/30"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I12"];
    c.PutValue("NextWeek");
}

// This method implements the TimePeriod conditional formatting type with NextMonth attribute.
private void AddTimePeriod_5(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I9:K10", Color.Maroon, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.NextMonth;
    Cell c = _sheet.Cells["I9"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/08/25"));
    c = _sheet.Cells["K10"];
    c.PutValue(DateTime.Parse("2008/07/30"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c = _sheet.Cells["I10"];
    c.PutValue("NextMonth");
}
// This method implements the TimePeriod conditional formatting type with LastWeek attribute.
private void AddTimePeriod_4(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I7:K8", Color.Linen, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.LastWeek;
    Cell c = _sheet.Cells["I7"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/07/25"));
    c = _sheet.Cells["K8"];
    c.PutValue(DateTime.Parse("2008/07/30"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I8"];
    c.PutValue("LastWeek");
}
// This method implements the TimePeriod conditional formatting type with LastMonth attribute.
private void AddTimePeriod_3(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I5:K6", Color.Linen, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.LastMonth;
    Cell c = _sheet.Cells["I5"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/06/26"));
    c = _sheet.Cells["K6"];
    c.PutValue(DateTime.Parse("2008/07/30"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I6"];
    c.PutValue("LastMonth");
}
// This method implements the TimePeriod conditional formatting type with Last7Days attribute.
private void AddTimePeriod_2(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I3:K4", Color.LightSteelBlue, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.Last7Days;
    Cell c = _sheet.Cells["I3"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/07/26"));
    c = _sheet.Cells["K4"];
    c.PutValue(DateTime.Parse("2008/08/30"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I4"];
    c.PutValue("Last7Days");
}
// This method implements the TimePeriod conditional formatting type with Today attribute.
private void AddTimePeriod_1(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I1:K2", Color.LightSlateGray, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.Today;
    Cell c = _sheet.Cells["I1"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Today);
    c = _sheet.Cells["K2"];
    c.PutValue(DateTime.Parse("2008/07/30"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c = _sheet.Cells["I2"];
    c.PutValue("Today");
}

// This method adds formatted conditions.
private FormatConditionCollection GetFormatCondition(string cellAreaName, Color color, Worksheet _sheet)
{
    // Adds an empty conditional formattings
    int index = _sheet.ConditionalFormattings.Add();
    // Get the formatted conditions
    FormatConditionCollection formatConditions = _sheet.ConditionalFormattings[index];
    // Get the cell area calling the custom GetCellAreaByName method
    CellArea area = GetCellAreaByName(cellAreaName);
    // Add the formatted conditions cell area.
    formatConditions.AddArea(area);
    // Call the custom FillCell method
    FillCell(cellAreaName, color, _sheet);
    // Return the formatted conditions
    return formatConditions;
}

// This method specifies the cell shading color for the conditional formattings cellarea range.
private void FillCell(string cellAreaName, Color color, Worksheet _sheet)
{
    CellArea area = GetCellAreaByName(cellAreaName);
    int k = 0;
    for (int i = area.StartColumn; i <= area.EndColumn; i++)
    {
        for (int j = area.StartRow; j <= area.EndRow; j++)
        {
            Cell c = _sheet.Cells[j, i];
            if (!color.IsEmpty)
            {
                Style s = c.GetStyle();
                s.ForegroundColor = color;
                s.Pattern = BackgroundType.Solid;
                c.SetStyle(s);
            }
            // Set some random values to the cells in the cellarea range
            int value = j + i + k;
            c.PutValue(value);
            k++;
        }
    }

}
// This method specifies the CellArea range (start row, start col, end row, end col etc.)
// For the conditional formatting
internal static CellArea GetCellAreaByName(string s)
{
    CellArea area = new CellArea();
    string[] strCellRange = s.Replace("$", "").Split(':');
    int column;
    CellsHelper.CellNameToIndex(strCellRange[0], out area.StartRow, out column);
    area.StartColumn = column;
    if (strCellRange.Length == 1)
    {
        area.EndRow = area.StartRow;
        area.EndColumn = area.StartColumn;
    }
    else
    {
        CellsHelper.CellNameToIndex(strCellRange[1], out area.EndRow, out column);
        area.EndColumn = column;
    }
    return area;
}