Formula Errors

How to Move Excel Numbers According to Start and End Dates

Muhammad TalhaMuhammad Talha Nov 10, 2026 868 views

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.

Move Excel Numbers According to Start and End Dates
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.
Before you start

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.

Solution 1Recommended

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.

1
Set up the timeline headers

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.

2
Enter the core logical formula

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.

3
Add weekend exclusions (Optional)

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,""),"").

4
Drag and copy the formula

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.

Use Nested IF and AND Formulas to Check Date Ranges
Absolute and Relative References: Pay close attention to the dollar signs ($) in the formula. $C13 locks column C but allows the row to change, while G$12 locks row 12 but allows the column to change. This is essential for the formula to work when dragged across the schedule.
Effortless Project Scheduling

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. 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. 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. 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.
Fully supports advanced logical functions like IF, AND, and complex date calculations.Seamless compatibility with Microsoft Excel (.xlsx) formats and formulas without data loss.Features built-in templates for Gantt charts and project management to save you time.Lightweight software that runs smoothly on Windows, Mac, and Linux systems.
microsoft office alternative - wps office

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.