How to Move Excel Numbers According to Start and End Dates
Question details
The user wants to populate cells with specific values (such as manpower) across a timeline or Gantt-style layout whenever the timeline's date falls within a specified task start and end date range.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a project schedule or resource allocation matrix where numbers need to automatically align with corresponding dates on a horizontal timeline.
- Observed behavior
- The user needs a logical formula that evaluates whether the current column's date is between the start and end date, while potentially skipping weekends, to output the correct assignment value.
Ensure your timeline header dates and the start/end date columns are formatted as actual Date values rather than text. Identify the specific cells containing your start dates, end dates, and the row serving as your timeline header before writing the formula.
Use Nested IF and AND Formulas to Check Date Ranges
Create a dynamic schedule by nesting IF and AND functions to verify if the timeline date falls within the task's start and end dates, populating the cell if the condition is met.
To distribute manpower or assignment values across a timeline, you must use absolute and relative references correctly. Locking the row for the timeline header and locking the column for the start/end dates ensures the formula evaluates the correct criteria as it is copied across the grid.
Ensure your timeline dates are arranged horizontally (e.g., in row 12, starting from G12). Enter your task start dates in column C and end dates in column D.
Click on the first cell of your schedule grid (e.g., G13) and enter the basic date-checking formula: =IF(AND(G$12>=$C13, G$12<=$D13), 2, ""). Replace the number '2' with your specific manpower number or the cell reference containing the manpower value.
If you have a row indicating days of the week (e.g., row 10 has "S" for Saturday/Sunday) and a global project start date in G6, modify the formula to check for those conditions: =IF(AND(G$10<>"S",$C13>=$G$6),IF(AND(G$12>=$C13,G$12<=$D13),2,""),"").
Press Enter to apply the formula. Select the cell, click and hold the fill handle at the bottom-right corner, and drag it across the columns and down the rows to populate your entire project schedule.

Create Dynamic Gantt Charts in WPS Spreadsheet
You can easily build complex project schedules, allocate manpower, and use advanced date formulas using WPS Spreadsheet. It provides a lightweight, highly efficient platform for data management and scheduling tasks.
- 1. Open your project schedule: Launch WPS Spreadsheet and open your existing project workbook or create a new one using a built-in project management template.
- 2. Apply the date range formula: Select the starting cell in your timeline grid and input your nested =IF(AND(...)) formula to automatically track start and end dates.
- 3. Enhance with Conditional Formatting: Highlight your schedule grid and use the Conditional Formatting tool in the Home tab to automatically apply colors to cells containing values, instantly creating a visual Gantt chart.

Frequently Asked Questions
How do I reference the manpower value from another cell instead of hardcoding a number?
Instead of typing a fixed number like '2' in the formula =IF(AND(G$12>=$C13, G$12<=$D13), 2, ""), replace it with the absolute column reference of your manpower data. For example, if manpower is in column E, use $E13 in the formula.
Why is my formula returning an error or leaving cells blank incorrectly?
Ensure your absolute and relative references are correct (e.g., $C13 locks the column, G$12 locks the row). Additionally, confirm that your timeline header row and your start/end columns contain valid Excel date serial numbers, not plain text.
Can I automatically skip weekends without using a dedicated row for days of the week?
Yes. You can use the WEEKDAY function within your AND condition. For example, add WEEKDAY(G$12, 2)<6 to your formula to ensure the timeline date is a Monday through Friday, which automatically skips Saturdays and Sundays.
How can I highlight the cells instead of displaying numbers in the schedule?
You can apply Conditional Formatting to your schedule grid. Use the formula =AND(G$12>=$C13, G$12<=$D13) as the formatting rule, and set a fill color to visually represent the task duration block without displaying the actual manpower numbers.




