Calculation Issues

Excel Formula to Return 1 or 0 for Employee Active Dates

Natalie TaylorNatalie Taylor Nov 18, 2026 869 views

Question details

Determine whether an employee was active at any point during a selected month and return a numeric 1 or 0.

Excel Formula to Return 1 or 0 for Employee Active Dates
Product
Microsoft Excel
Device & OS
not provided
Scenario
An employee may start or leave partway through a month, or have no termination date.
Observed behavior
The desired result is 1 when the employee's active dates overlap the selected month and 0 otherwise.
Before you start

Enter valid spreadsheet dates in C2 for the start date, D2 for the termination date, and F1 for any date in the month being tested. Leave D2 blank if the employee has no termination date.

Solution 1Recommended

Check Whether Employee Dates Overlap the Selected Month

Use this formula to count any overlap with the month, including a mid-month start or termination.

The formula treats both the start and termination dates as inclusive. A blank termination date means the employee remains active indefinitely; a blank start date returns 0.

1
Enter the formula

In cell F2, enter =--AND($C2<>"",$C2<=EOMONTH(F$1,0),OR($D2="",$D2>=DATE(YEAR(F$1),MONTH(F$1),1))).

2
Check the result

Press Enter. The formula returns 1 if the employee was active on at least one day in the month in F1; otherwise, it returns 0.

3
Fill the schedule

Enter dates for other months in the header row, then drag the fill handle from F2 across and down to apply the formula to those months and employees.

Check Whether Employee Dates Overlap the Selected Month
How the formula works: EOMONTH finds the last day of the selected month, and DATE finds its first day. The double minus converts the TRUE or FALSE result to 1 or 0.
Calculate active months in WPS

Calculate Employee Active Months in WPS Spreadsheets

Use the same date-overlap formula in WPS Spreadsheets to flag each month in which an employee was active.

  1. 1. Open your spreadsheet: Open WPS Spreadsheets and select Open to load your employee workbook.
  2. 2. Set up the dates: Enter the start date in C2, the termination date in D2 if applicable, and a date in the selected month in F1.
  3. 3. Add the formula: Select F2 and enter =--AND($C2<>"",$C2<=EOMONTH(F$1,0),OR($D2="",$D2>=DATE(YEAR(F$1),MONTH(F$1),1))).
  4. 4. Apply it to the table: Press Enter, then drag the fill handle across the month columns and down the employee rows.
Return numeric 1 or 0 for each employee and monthInclude employees who start or leave partway through a monthOpen and edit Microsoft Excel XLSX and XLS filesFill formulas across a monthly schedule
microsoft office alternative - wps office

Frequently Asked Questions

Does an employee who starts halfway through the month count as active?

Yes. The formula returns 1 if the start date in C2 is on or before the last day of the month in F1, provided the termination date does not fall before that month.

What happens if the termination date is blank?

Leave D2 blank for an employee who has not been terminated. The formula treats the employee as active indefinitely after the start date.

Is the termination date included as an active day?

Yes. The comparison with the first day of the month uses >=, so an employee whose termination date falls on the first day of the month returns 1 for that month.

Why does the formula return 0 when the start date is blank?

The $C2<>"" check requires a start date. Enter a valid date in C2 before calculating the employee's active months.

Does F1 have to contain the first day of the month?

No. Enter any valid date in the month in F1. The formula calculates the first and last days of that month automatically.