VBA & Macro Problems

Create an Excel Safety Calendar to Count Consecutive Safe Days

Phi Hung VoPhi Hung Vo Nov 18, 2026 869 views

Question details

Create a safety calendar that updates the start date of the current safe period and counts consecutive days without an incident.

Create an Excel Safety Calendar to Count Consecutive Safe Days
Product
Microsoft Excel
Device & OS
not provided
Scenario
A safety calendar records dates and incident statuses, and the user wants a current safe-days count.
Observed behavior
The safe-period start date and consecutive safe-days count should update as incident dates are entered and each new day begins.
Before you start

Decide the first date to include in the count and enter calendar dates as real spreadsheet dates. The formulas below assume that each incident is recorded with the exact status Incident and that unrecorded days are safe.

Solution 1Recommended

Calculate safe days with Excel formulas

Use a date-and-status list to calculate the current safe-period start date and streak without a macro.

This example counts the first safe day as one and an incident day as zero. It ignores future incidents and incidents before the chosen starting date.

MAXIFS is available in Excel 2019 and later and Microsoft 365.

1
Set up the calendar list

In a worksheet, enter Date in A1 and Status in B1. Enter dates in A2:A1000 and mark incident dates with Incident in the corresponding cells in B2:B1000.

2
Enter the starting date

Enter the first date to include in the safety count in E2. Format E2 as a date using Home > Number Format.

3
Calculate the safe-period start

Enter =IF(OR(E2="",E2>TODAY()),"",MAX(E2,MAXIFS(A2:A1000,B2:B1000,"Incident",A2:A1000,"<="&TODAY())+1)) in E3. Use Home > Number Format to display E3 as a date.

4
Calculate consecutive safe days

Enter =IF(E3="","",MAX(0,TODAY()-E3+1)) in E4. Add Incident to a date's status in column B whenever an incident occurs.

Calculate safe days with Excel formulas
When the count updates: An incident today makes the count zero and sets E3 to tomorrow, the next possible safe-period start. TODAY updates when the workbook recalculates; check Formulas > Calculation Options > Automatic if the count does not refresh.
WPS Spreadsheets

Track consecutive safe days in WPS Spreadsheets

Build the same date-and-status tracker in WPS Spreadsheets, using formulas to update the safe-period start date and current count.

  1. 1. Create the incident list: Open WPS Spreadsheets and create a workbook. Enter Date in A1 and Status in B1, then record dates in A2:A1000 and use Incident for incident dates in column B.
  2. 2. Set the first counting date: Enter the first date to include in E2 and use the Home tab's number format controls to display it as a date.
  3. 3. Find the safe-period start: Enter =IF(OR(E2="",E2>TODAY()),"",MAX(E2,MAXIFS(A2:A1000,B2:B1000,"Incident",A2:A1000,"<="&TODAY())+1)) in E3, then format E3 as a date.
  4. 4. Display the safe-days count: Enter =IF(E3="","",MAX(0,TODAY()-E3+1)) in E4. Update the status in column B whenever an incident occurs.
Track consecutive safe days without writing a macroOpen and save Microsoft Excel XLSX workbooksUpdate the count as incident records changeUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

What happens if there are no incidents in the list?

The formula uses the starting date in E2, and E4 counts from that date through today, inclusive.

Does the count reset on the day of an incident?

Yes. Enter Incident beside today's date in column B. E4 becomes zero, and E3 displays tomorrow as the next possible safe-period start.

Why is the safe-days count not changing?

Check that E2 and the dates in column A are real dates, and that incident statuses say Incident. In Excel, also check Formulas > Calculation Options > Automatic.

Can I use a visual calendar instead of a date list?

Yes, but the formulas shown need dates and statuses in columns A and B. Copy or link the calendar's dates and incident statuses to that list, or use a macro tailored to the calendar layout.