Calculating Actual Progress vs Planned Progress base on Today's Month & Year

With reference to the following screenshot, I am trying to capture the Actual Progress & Planned Progress on row 1 of the respective column base on Today's Month and Year.

For example, today is January 2024, row 1 for Planned Progress 2 % should show 0%, and row 1 for Actual Progress 2 % should show 0%.

If today is June 2024, row 1 for Planned Progress 2 % should show 30%, and row 1 for Actual Progress 2 % should show 50%.

If today is August 2024, row 1 for Planned Progress 2 % should show 40%, and row 1 for Actual Progress 2 % should show 50%.

I am trying to index match the children rows with Today's Month and Year for Actual Progress 2 %:

=INDEX(children([Actual Progress 2 %], MATCH(AND(Month(@cell) = MONTH(Today()), Month:Month, Year(@cell) = Year(Today()))), 0))

I get an error message: #UNPARSEABLE

I believe the formula to be used in Planned Progress 2 % and Actual Progress 2 % should be similar.

Any assistance would be very much appreciated.



Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!