How to keep the previous historical project planned progress (StatisPlanned%Progress)

IPC

Hi,

I am using a template where the Planned % Progress is kept updated to date. It is based on Planned Start Date vs current date.

However, I also need to produce a monthly report that shows what WAS the planned % in the previous reporting months.

Can anyone share an approach?

My approach is to create a monthly version of the project and manually type the reflected Planned % progress as of that month end, hence it will no longer change as the formula is over ridden.

Any thoughts to share?

Thanks,

IC

#StaticPlanned%Progress

Genevieve P.

Hi @IPC

Personally, I would use a Copy Row workflow to copy that row to another sheet on a monthly cadence.

This would copy the row over automatically so you don't have to think about it.

However this will depend on what sort of formulas you're using for your calculations, and if they'll keep updating on the second sheet or not.

If this hasn't helped, it would be useful to learn more about your specific sheet and process. Can you post screen captures of the sheet, and let us know what formulas you're using? (But please block out sensitive data)

Cheers,

Genevieve

IPC

Dear @Genevieve P.

The column "Planned % Complete" calculates the planned % to date, hence it changes daily.

=ROUND(IF(TODAY() < [Planned Start Date]@row, 0, IF(TODAY() >= [Planned End Date]@row, 1, NETWORKDAYS([Planned Start Date]@row, TODAY()) / NETWORKDAYS([Planned Start Date]@row, [Planned End Date]@row))), 2)

However, at a certain cut-off date (i.e. first of every month), I need to have that value fixed, so that if there is a delay in the update from the project owners, still the performance (i.e. Actual % progress / Planned Percent Progress) will remain the same, otherwise, everyday that moves the Performance goes down. And based on that, we keep a track of the previous month's performance.

thanks for your advice in advance

IPC

Genevieve P.

Hi @IPC

Thanks for clarifying; I believe a Copy Row workflow would still be the best way to track historical data, in this case!

What you could do is add a new column to your current sheet and call it something like "Today's Date". Then use a Record a Date workflow to add today's date to every row in that column early in the morning on a daily basis.

This would allow you to change your formula to look at the date in that cell instead of using TODAY(), like so:

=ROUND(IF([Today's Date]@row < [Planned Start Date]@row, 0, IF([Today's Date]@row >= [Planned End Date]@row, 1, NETWORKDAYS([Planned Start Date]@row, [Today's Date]@row) / NETWORKDAYS([Planned Start Date]@row, [Planned End Date]@row))), 2)

Then you can copy rows over to your second sheet on a monthly cadence. As long as you don't have this Record a Date workflow in your second sheet, that "Today's Date" will stay static to the date that you copied the row. Does that make sense?

Cheers,

Genevieve