Excel Formula to Display Jobs Between Start and End Dates
Question details
Create a formula that displays a job for every week-ending date that is on or after the job start date and on or before the job end date.
- Product
- Microsoft Excel for Microsoft 365
- Device & OS
- not provided
- Scenario
- The user is building a schedule with job names, start dates, end dates, and week-ending dates in separate ranges.
- Observed behavior
- Each schedule cell should show the matching job name when its column's week-ending date falls within that job's date range; otherwise, it should remain blank.
Confirm that the Start, End, Jobs, and WeekOf ranges contain real Excel dates rather than date-looking text. Make sure each named range covers the same set of job rows, and that WeekOf contains the week-ending dates across the schedule.
Use a Microsoft 365 dynamic array formula for the full job schedule
Use this option in Microsoft 365 when you want one formula to spill a complete jobs-by-weeks schedule automatically.
This formula checks both date boundaries for every job and week-ending date. It returns the job name only when the weekly date is greater than or equal to the start date and less than or equal to the end date.
In Excel, open Formulas > Name Manager and create names for the job-name range as Jobs, the start-date range as Start, the end-date range as End, and the week-ending date row as WeekOf.
Click the first blank cell where the job-by-week results should begin. Ensure that the cells to the right and below are empty so the dynamic array can spill.
Enter =LET(height,ROWS(Jobs),width,COLUMNS(WeekOf),GetJobs,LAMBDA(r,c,LET(weekly_date,INDEX(WeekOf,,c),first_date,INDEX(Start,r),second_date,INDEX(End,r),job,INDEX(Jobs,r),IF(AND(weekly_date>=first_date,weekly_date<=second_date),job,""))),MAKEARRAY(height,width,GetJobs)) and press Enter.
Use a standard IF and AND formula when dynamic arrays are unavailable
Use this option when MAKEARRAY is not available or when you prefer to fill a traditional schedule formula across rows and columns.
Build a Date-Range Job Schedule in WPS Spreadsheets
WPS Spreadsheets can create the same weekly job schedule with a simple IF and AND formula. Enter the formula once, then fill it across your week-ending columns and down your job list to display jobs active during each date range.
- 1. Arrange the schedule data: In WPS Spreadsheets, place job names in column A, start dates in column B, end dates in column C, and week-ending dates across row 1 beginning in column D.
- 2. Select the first schedule cell: Click cell D2, which intersects the first job row and the first week-ending date column.
- 3. Enter the date-range formula: Type =IF(AND(D$1>=$B2,D$1<=$C2),$A2,"") in the formula bar and press Enter.
- 4. Copy the formula across the schedule: Select D2 and drag its fill handle right across the week columns, then drag down for all job rows.
Frequently Asked Questions
Why does a job not appear for its start or end week?
Check that the formula uses inclusive comparisons: >= for the start date and <= for the end date. Also confirm that the week-ending date and both job dates are stored as actual dates.
Can one job appear in several weekly columns?
Yes. When the week-ending date in each column falls between that job's start and end dates, the formula returns the job name in every matching weekly cell.
What formula should I use without LET and MAKEARRAY?
Use a fillable formula such as =IF(AND(D$1>=$B2,D$1<=$C2),$A2,""). Enter it in the first schedule cell, then copy it across and down.
How do I show a symbol instead of the job name?
Replace $A2 in the IF formula with the text you want to display, such as "X" or "Active": =IF(AND(D$1>=$B2,D$1<=$C2),"X","").
Do the week-ending dates need to be Sundays or Fridays?
No. The formula works with any consistent weekly date, including Friday, Saturday, or Sunday week endings, as long as those dates are compared to the job start and end dates.
