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

Options
✭✭

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.

Tags:

• ✭✭✭✭✭✭
edited 02/08/24
Options

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.

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options