Create an Excel Safety Calendar to Count Consecutive Safe Days
Question details
Create a safety calendar that updates the start date of the current safe period and counts consecutive days without an incident.

- 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.
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.
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.
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.
Enter the first date to include in the safety count in E2. Format E2 as a date using Home > Number Format.
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.
Enter =IF(E3="","",MAX(0,TODAY()-E3+1)) in E4. Add Incident to a date's status in column B whenever an incident occurs.

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. 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. 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. 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. 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.

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.




