Calculate a Target Every Eight Days from a Date in Excel
Question details
Calculate a target that increases by 5 for each completed eight-day period after the date in G3.

- 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.
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.
Calculate the Target with TODAY and TRUNC
Use this formula to start at 5 and add 5 for every completed eight-day period.
Enter a valid date in cell G3 and confirm it is on or before today.
Select N3, enter =(TRUNC((TODAY()-G3)/8)+1)*5, and press Enter.
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.

Return Zero Before the Start Date
Use an IF formula if G3 may contain a future date and the target should remain zero until that date.
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. Open the workbook: In WPS Spreadsheets, select Open and choose your Excel workbook.
- 2. Check the start date: Select G3 and verify that it contains a valid date on or before today.
- 3. Enter the formula: Select N3, enter =(TRUNC((TODAY()-G3)/8)+1)*5, and press Enter.
- 4. Save the workbook: Select Save to keep the formula in your workbook.

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.




