Formula issue

Susan van Niekerk

Hi Braine Trust

I need some help with a formula

I have Mobilization Date and need to create reports that will show me a list of people who have mobilized -

Any one Mobing Week Prior to the Mob Date

Any one Mobing The Week of the Mob Date

A 2 week Forecast ( so two weeks prior to the Mob Date

A 3 week forecast (so three weeks prior to Mob Date)

Ideally thee would create a dropdown by I dont think that is doable

I just need some sort of filtering to enable me to build 4 report pulling the info - and we cant use today's date as it all needs to be measured of the Mobs date which will be fairly static

Thanh

Hi Susan,

I'm Thanh from wedocom, strategic partner of smartsheet. You can try to create four columns in your sheet :

To address this issue in Smartsheet, here’s a solution that can be implemented using additional columns and formulas to handle the filtering criteria. Follow these detailed steps:

1- Add the following columns to your Smartsheet to calculate the criteria:

• Week Before Mob Date: To identify people mobilized a week before the mobilization date.

• Week of Mob Date: To identify people mobilized during the week of mobilization.

• 2 Week Forecast: To identify people mobilized up to 2 weeks before the date.

• 3 Week Forecast: To identify people mobilized up to 3 weeks before the date.

• 2 - Use the following formulas to populate these columns.

• 1. One Week Before Mobilization Date

• Column: Week Before Mob Date

• =IF(AND([Mobilization Date]@row - [Your Date Column]@row >= 1, [Mobilization Date]@row - [Your Date Column]@row <= 7), "Yes", "No")

• etc…. for the others columns

• Create a report for each criterion: Use the Report tool in Smartsheet to display only the rows where the corresponding column contains "Yes." For example:For "One week before mobilization," filter rows where Week Before Mob Date = "Yes."Repeat this for the other criteria.

• I can help you if you want by google meet : thanh@wedocom.io