Sheet changes biweekly

Nicole_Codi17

Hi all,

I have a sheet that goes out biweekly for vendors to update and make changes on sheet. I am looking to create a line chart that measures bi weekly changes (if any). There is really no status column to measure which would be easy to measure changes, it is overall changes to sheet, if any.

Thanks in advance!

KPH

How about creating another sheet and building a table using formula that count rows where the modified date in the vendor sheet is within the last 2 weeks, within the last 4 weeks, within the last 6 weeks, etc.? Then you could create a chart from that table.

Nicole_Codi17

Thank you, KPH! Any suggestions how to lay table out? Date range along top?

Nicole_Codi17

I am getting unparseable error.

KPH

Hi @Nicole_Codi17

Thank you, KPH! Any suggestions how to lay table out? Date range along top?

I would suggest a column with the dates in - that way you can format it as a date column, you cannot format a row as a date format. It is good practice to keep things that are the same in the same column.

If you format the column as a date column, you could the TODAY formula within that column to complete the date labels which would mean they automatically update and you don't risk the axis label showing you something different to what the formula shows. If you put TODAY-14 as the label and then refer to that cell in your COUNTIF you don't need to worry about updating the dates.

It also means the COUNTIF formula will be the same for every row, just referring to the date column and not needing any date ranges to be specified within the formula.

I use this in the first date cell =TODAY(-14)

And then =Date1 - 14 in the row below and drag that down to create the list of dates

Every day this will update and show the most recent 2 week period at the top

This would produce a graph like this

I am getting unparseable error.

1. You have an extra closing parenthesis at the end of your formula.
2. The cross sheet reference should be just {reference name} not {reference name}:{reference name}