Hello,
Hopefully someone can help me here. I'm in the process of automating a report that gets written and I'm down to one final formula that I need help with.
Currently I use this formula to calculate a 2 month run rate
=(((N14/1)*5)+O14)/2
The '5' represents the number of weeks left in a particular month. This number changes depending on when you run the report, and depending on how many weeks are in a FISCAL month. This particular company publishes a Calendar to highlight when their fiscal months begin and end, so it's very predictable.
The report I run is always run on Sunday, so the calculation is always done at the beginning of the week.
I need a way to replace the '5' in the formula with a formula that will automatically enter in the appropriate number (1, 2, 3, 4 or 5) depending on how many weeks are left in that fiscal month.
The forumula, as I see it would need to make the calculation based on the date the report is run, and then determine how many weeks are left in the month. I'm racking my brain, but I can't seem to get my hands around how to make that happen.
As an example, the Month of June actually started on May 26th, and ends on June 27th, so June has 5 weeks. Today is June 14th, the next report will run tomorrow (June 15th), so there will be 2 weeks left in the month (so the '5' in the formula above would need to be changed to a 2).
To be more exact, the last day of the Fiscal Month of May was May 24th, the Last Day of the Fiscal Month of June is June 28th.
Can anyone suggest a solution to this problem? Or point me in the direction of where I might find the solution? I can't be the first one to look for this.
Thanks.. Any help would be greatly appreciated.
Mike
Hopefully someone can help me here. I'm in the process of automating a report that gets written and I'm down to one final formula that I need help with.
Currently I use this formula to calculate a 2 month run rate
=(((N14/1)*5)+O14)/2
The '5' represents the number of weeks left in a particular month. This number changes depending on when you run the report, and depending on how many weeks are in a FISCAL month. This particular company publishes a Calendar to highlight when their fiscal months begin and end, so it's very predictable.
The report I run is always run on Sunday, so the calculation is always done at the beginning of the week.
I need a way to replace the '5' in the formula with a formula that will automatically enter in the appropriate number (1, 2, 3, 4 or 5) depending on how many weeks are left in that fiscal month.
The forumula, as I see it would need to make the calculation based on the date the report is run, and then determine how many weeks are left in the month. I'm racking my brain, but I can't seem to get my hands around how to make that happen.
As an example, the Month of June actually started on May 26th, and ends on June 27th, so June has 5 weeks. Today is June 14th, the next report will run tomorrow (June 15th), so there will be 2 weeks left in the month (so the '5' in the formula above would need to be changed to a 2).
To be more exact, the last day of the Fiscal Month of May was May 24th, the Last Day of the Fiscal Month of June is June 28th.
Can anyone suggest a solution to this problem? Or point me in the direction of where I might find the solution? I can't be the first one to look for this.
Thanks.. Any help would be greatly appreciated.
Mike
