Formula Errors

Calculate a Target Every Eight Days from a Date in Excel

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 868 views

Question details

Calculate a target that increases by 5 for each completed eight-day period after the date in G3.

Calculate a Target Every Eight Days from a Date in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
The target in N3 will be used alongside attendance data in M3 and O3.
Observed behavior
The desired target starts at 5 on the date in G3 and increases by 5 after each completed eight-day period.
Before you start

Check that G3 contains a valid Excel date on or before today, rather than a date stored as text. Decide whether the target should start at 5 on that date; the formula below uses that starting value.

Solution 1Recommended

Calculate the Target with TODAY and TRUNC

Use this formula to start at 5 and add 5 for every completed eight-day period.

1
Enter the start date

Enter a valid date in cell G3 and confirm it is on or before today.

2
Add the target formula

Select N3, enter =(TRUNC((TODAY()-G3)/8)+1)*5, and press Enter.

3
Check the result

Compare N3 with the elapsed days since G3: it should show 5 for days 0–7, 10 for days 8–15, and 15 for days 16–23.

Calculate the Target with TODAY and TRUNC
Using the target with attendance: This formula calculates only the target in N3. It does not compare or calculate the attendance values in M3 and O3.
Calculate targets in WPS Spreadsheets

Calculate the Eight-Day Target in WPS Spreadsheets

Open your workbook in WPS Spreadsheets and use the same TODAY and TRUNC formula to calculate the target from the date in G3.

  1. 1. Open the workbook: In WPS Spreadsheets, select Open and choose your Excel workbook.
  2. 2. Check the start date: Select G3 and verify that it contains a valid date on or before today.
  3. 3. Enter the formula: Select N3, enter =(TRUNC((TODAY()-G3)/8)+1)*5, and press Enter.
  4. 4. Save the workbook: Select Save to keep the formula in your workbook.
Calculate a target that increases every eight daysOpen and edit Microsoft Excel XLS and XLSX filesUse familiar spreadsheet cells and formulasWork with a free, easy-to-use office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula show 5 on the start date?

The +1 in =(TRUNC((TODAY()-G3)/8)+1)*5 gives an initial target of 5. The target then increases by 5 after each completed eight-day period.

How can I start the target at zero instead?

Enter =TRUNC((TODAY()-G3)/8)*5 in N3. For a start date on or before today, the result is 0 for days 0–7 and 5 for days 8–15.

What if the date in G3 is in the future?

If the target should be zero before the start date, enter =IF(TODAY()<G3,0,(TRUNC((TODAY()-G3)/8)+1)*5) in N3.

Why is the formula returning a date error?

Check that G3 contains a valid spreadsheet date rather than text. Re-enter the date in G3 and format the cell as a date if needed.

Does the formula use the attendance data in M3 and O3?

No. The formula calculates the target in N3 from G3 and today's date. A separate formula is needed to compare that target with attendance data.