Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default 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()

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
calculate percentage of month passed Anita Excel Worksheet Functions 6 April 4th 23 02:10 PM
Date passed formula sweetcorn999 Excel Worksheet Functions 1 June 9th 10 09:12 AM
calculating percentage of year passed (for sales reports) morneauja Excel Worksheet Functions 1 April 22nd 09 11:59 PM
IF Formula to calculate whether date has passed Janelle Lister Excel Discussion (Misc queries) 4 October 19th 06 03:32 PM
Graph Help- 12 month graph, but only want months that have passed coal_miner Charts and Charting in Excel 4 June 3rd 05 03:03 PM


All times are GMT +1. The time now is 04:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"