Formula to add up hours in a 4-week period?

Ask for Help
1

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?

2

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
3

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?