Month to Date over prior and Week to Date over prior automation

[ZDR23]

Hello.

I am currently using formulas to calculate % change for week-to-date / prior week-to-date numbers and month-to-date / prior month-to-date.

This data is updated daily and requires me to manually adjust the days.

Is there a way to automatically calculate as new data is added each day? I've attached example file.

[Estevaoba]

Welcome to the forum.

I have created dynamic ranges with formula like this:

Please Login or Register  to view this content.

The second INDEX portion "INDEX(C$2:C$100,MATCH(MAX($B$2:$B$100),$B$2:$B$100,0)-4)" determines the row the range starts, which corresponds to the MAX date minus 4, (-4).
Then we can infer that the third INDEX portion "INDEX(C$2:C$100,MATCH(MAX($B$2:$B$100),$B$2:$B$100,0))" determines the row the range ends, i.e., the row with the last date.
The first INDEX function combines the other two, and the zero as row number establishes that all rows in the range are considered.

So, for the first SUM, I replace the hard-coded range C25:C29 with this formula: "INDEX(INDEX(C$2:C$100,MATCH(MAX($B$2:$B$100),$B$2:$B$100,0)-4):INDEX(C$2:C$100,MATCH(MAX($B$2:$B$100),$B$2:$B$100,0)),0))"
For the prior week range, I subtract 5 by changing the -4 to -9 and zero to -5, resulting:
"INDEX(INDEX(C$2:C$100,MATCH(MAX($B$2:$B$100),$B$2:$B$100,0)-9):INDEX(C$2:C$100,MATCH(MAX($B$2:$B$100),$B$2:$B$100,0)-5),0))"
I have done it in colum K for the first two cases, so you can compare.

The problem here is when there are less than 10 rows of data, as the formula will result a VALUE error, but since there must be at least 2 weeks data to compare, I guess that will not be a problem.

Please run some test and let us know how it goes.

[protonLeah]

Units Ordered (5 day week?)

Please Login or Register  to view this content.

Units Returned

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Pl see the file with formulas in Columns J and K.

[ZDR23]

Hi Estevaoba,

Thank you for attempting this. The issue here arises when new data is added with each new week. The formula must adjust based on the current day/week and to compare over the prior week as constantly updating.

For example, I've now added data for 12/13, which means the formula must compare 12/13 over 12/6 and also adjust the MTD to add in the new date. Please see attached.

[ZDR23]

Hi kvsrinivasamurthy,

Same here where it is not considering the new date added for example 12/13. It is not updating with new week over week / month over month calculation.

[ZDR23]

Sometimes may be 3 or 4 day week depending on holidays and closures. So I would need the formula for week over week to update as each new day is added and compare to prior week. For month over month, the comparison must be for same number of working days.

[Estevaoba]

Hi Estevaoba,
For example, I've now added data for 12/13, which means the formula must compare 12/13 over 12/6 and also adjust the MTD to add in the new date. Please see attached.

Hello, ZDR23. You're welcome.
What cells/ranges are to be compared when it is 12/14?

[JohnTopley]

You need to define "Week": is it 5 working days Monday-Friday ? or 7 days "Sunday to Saturday" ??? and what do want to do if (whatever the definition) 2 consecutives weeks [mpnths] differ with number of entries (holidays/missing data) ?

For month over month, the comparison must be for same number of working days.

So this does not apply to "Weeks" ?

[ZDR23]

Hello, ZDR23. You're welcome.
What cells/ranges are to be compared when it is 12/14?

Estevaoba, it would need to compare 12/13 & 12/14 over 12/6 & 12/7. For month over month it would need to include the new day and also an additional day from the previous month.

I've added weekday, week # and month # to the report which may possibly help.

[ZDR23]

You need to define "Week": is it 5 working days Monday-Friday ? or 7 days "Sunday to Saturday" ??? and what do want to do if (whatever the definition) 2 consecutives weeks [mpnths] differ with number of entries (holidays/missing data) ?

So this does not apply to "Weeks" ?

John, it is 5 working days Monday - Friday.

For the week over week comparison, I would need the calculation to factor in any holidays (that particular day would not be included in the data). One week may have 5 days of data, while another may have 3 or 4 depending on the given month.

For the month over month comparison, each month will have a given amount of working days. So, as each day is added, the range is to be adjusted based on that number of days.

[JohnTopley]

See attached

in L2

=SUM(OFFSET($C$1,MATCH(MAX($E$2:$E$31),$E$2:$E$31,0),,COUNTIF($E$2:$E$31,MAX($E$2:$E$31))))

Similar for others

Change max range to suit

does NOT solve the issue of year ends e.g month 12/2021 with month 1/2022

[ZDR23]

See attached

in L2

=SUM(OFFSET($C$1,MATCH(MAX($E$2:$E$31),$E$2:$E$31,0),,COUNTIF($E$2:$E$31,MAX($E$2:$E$31))))

Similar for others

Change max range to suit

does NOT solve the issue of year ends e.g month 12/2021 with month 1/2022

Thank you John, it appears this logic does work. Is there a way to fix the issue of year ends?

[JohnTopley]

See attached Sheet2 which is a "Trial" based on date to see if it gives correct results: I will do a "year-end" rollover test if this approach is acceptable.

in I2

=SUM(OFFSET($C$1,MATCH(TODAY(),$B$2:$B$96,1),,-WEEKDAY(TODAY(),2)))

For "Week": it starts from Monday until "day" of Today()

For "Month" starts at first of month(Today()) and counts number work days using NETWORKDAYS.INTL.

For current/previous months it use "current month" days.

In November we have a missing date (11/!!/2021) but counts 11 days (blue colouring in column G.

UPDATE:

Consider making holidays etc entries with values of zero