Hello Liam,
I understand all the formula you use it to build dates. The only thing that I don't really understand is what represent the "Reporting Month Factor". Could you provide an English definition of that.
Thanks in advance for your help.
By the way your course is awesome.
Stefano
Hi Stefano,
I am glad you are finding the course useful.
"Reporting Month Factor" is an interesting element. It's essentially an interim calculation to identify which period number in a reporting cycle is the end period. I imagine that's as clear as mud too.
Imagine you reported quarterly (i.e. every three months) in February, May, August and November. These months would represent the second month of each three month cycle. This is what the Reporting Month Factor calculates. Here, it would be 2:
=MOD(Month No of February (2) - 1, 3) + 1 = 2
=MOD(Month No of May (5) - 1, 3) + 1 = 2
=MOD(Month No of August (8) - 1, 3) + 1 = 2
=MOD(Month No of November (11) - 1, 3) + 1 = 2.
The -1 inside MOD followed by +1 outside MOD is a trick to get the possible values of 1, 2 and 3 here rather than 1, 2 and zero (0).
This number is needed to calculate the end months for each reporting cycle.
I hope this makes sense.
Kind regards
Liam
Your explanation makes it all clear.
Really thank you
Stefano
You're welcome - enjoy the course!
L
