Percentage of month passed formula
 

I have a dashboard with the estimated monthly budgets for Q2/14. Every week, the dashboard is updated with month to date costs and the cost variance is high because the estimated monthly budget is calculating for the whole month of April, May, June (no matter what day of the month it is currently).

I need a formula to calculate the percentage of the month passed within that given month (and not to exceed the month).

For example, since today is the 5th of May, 16% of the month has passed. If I had a monthly budget of $1M, then my estimated monthly budget should be $161K on this day.

Also, now that April has passed, it shouldn't exceed 100%

Thanks in advance!

Percentage of month passed formula
 

On Mon, 5 May 2014 16:43:56 -0700 (PDT), Mary128 wrote:

I have a dashboard with the estimated monthly budgets for Q2/14. Every week, the dashboard is updated with month to date costs and the cost variance is high because the estimated monthly budget is calculating for the whole month of April, May, June (no matter what day of the month it is currently).

I need a formula to calculate the percentage of the month passed within that given month (and not to exceed the month).

For example, since today is the 5th of May, 16% of the month has passed. If I had a monthly budget of $1M, then my estimated monthly budget should be $161K on this day.

Also, now that April has passed, it shouldn't exceed 100%

Thanks in advance!

With some date in A1, the percentage of the month would be:

=DAY(A1)/DAY(EOMONTH(A1,0))

If you need to be concerned about "today", merely replace A1 with TODAY()