Hello, we are making a timesheet app.

We’d like to create a formula that adds up the total hours worked from a 4-week period, but we only want to include the past 4 CONSECUTIVE weeks. How can we make a formula for this? I’ve included screenshots below showing my current attempts-- basically, I’ve multiplied my weekly totals by 4, but since I don’t know how to limit it so that we only have the past 4 weeks, I have ALL weekly totals multiplied by 4. How can I solve this?

4 weeks from *now*, or 4 weeks from a specific date? (ie yesterday, last Monday, etc)

I’ll assume the later.

- Create a date column to store that value
- Next create a math column to calculate the date 4 weeks prior to that. Use the formula
`date - 28`

where`date`

is the value in the first column - Now create an if-then-else column that compares the timesheet date to the respective start and end dates of your 4 week period (the first two columns).
- If it’s before the start date, return empty
- If it’s after the end date, return empty
- Else return
`true`

(it’s in the goldilocks zone)

- Now you can use that column as a filter to only return rows within your 4 week window.

Edit: ugh… just re-read and realised you want to sum the hours… okay, a couple of extra steps required:

- create a template column that returns the single word
`true`

- create a multi-relation column that joins the template column to the if-then-else column mentioned above
- now create a rollup column, that does a sum of hours through that relation.

1 Like

So you mean this 4-week can be user-specific right?

Say I work week 1, 2, 7, 100, 500 then we look at 2, 7, 100, 500 data?