Formula to Calculate assets from Week Commencing Date from previous week.

bee2

I’m trying to work out a formula to calculate how many assets were created in the previous week from another sheet. I’m using a Week Commencing date which is the first Monday of each week and entries are being dividing into w/c based on when they were submitted to Smartsheet.

How can I return the number of assets from the previous week commencing using the TODAY formula. I want this to be updated each week so I’m always looking at the previous week’s data so I can report on it. Do I need to pull results ‘between’ 7 and 13 days ago? I have the below formula but it’s only returning 0 results when it should be 2.

=SUMIFS({Broadcast-# of Assets}, {Broadcast-Delivery}, $[Primary Column]@row, {Broadcast-FY}, ($[Primary Column]$2), {Broadcast-Week Commencing}, =TODAY(-7))

KPH

He @bee2

=TODAY(-7)

means only the rows where the week commencing date is exactly equal to 7 days before today. So if your week commencing is on a Monday and you run this on a Tuesday, Wednesday, Thursday... the result will be 0. You could use greater than and equals instead. Ie the week commencing is on or after today - 7. Try this and see if it does what you want

>=TODAY(-7)

If you have broadcast weeks that are in the future, you may need to add another range and criteria to exclude dates after today.

{Broadcast-Week Commencing}, < TODAY()

bee2

Thanks for this. My issue is that I want to search for anything that from the latest week commencing date so it can’t be only 7 days ago but I need to give a range. Is it possible to create a reference for a range between 7 and 13 days ago?

If w/c starts on a Monday, I think this is enough of a range to find all entries that contain the previous week details. Future week’s aren’t a concern as it will always be retrospective data.

KPH

You can add a range by adding two criteria like this

{Broadcast-Week Commencing}, >=TODAY(-13), {Broadcast-Week Commencing}, <=TODAY(-7)

The first says the week commencing date must be AFTER or on the date 13 days ago.

The second says the week commending date must be BEFORE or on the date 7 days ago.

The formula will only return rows where both things are true.

So, this will give you all rows that have a week commencing date that is 7 or more days ago but less than 13.

Have a play with both versions, try different numbers, and see what comes back.