Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 71
Default calculate percentage of month passed

I want to know how many days have lapsed in a month, every month, as a
percentage. So far we are doing it week by week, ie after the first week has
gone we say that 25% of the month has gone. We need it to be more acurate.
The problem we are having is that each month has a different amount of days.
Is there a formula we can use for this and one for the percentage of the
month left?

Thank you

Anita
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: calculate percentage of month passed

Hi Anita,

Yes, there is a formula you can use to calculate the percentage of the month that has passed. Here are the steps:
  1. First, you need to determine the total number of days in the current month. You can do this by using the
    Formula:
    EOMONTH 
    function. For example, if you want to calculate the percentage of days that have passed in the current month, you can use the following formula:

    =EOMONTH(TODAY(),0)-DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1

    This formula will give you the total number of days in the current month.
  2. Next, you need to determine the number of days that have passed in the current month. You can do this by using the
    Formula:
    TODAY 
    function to get the current date, and then subtracting the first day of the month from it. For example:

    =TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1

    This formula will give you the number of days that have passed in the current month.
  3. Finally, you can calculate the percentage of the month that has passed by dividing the number of days that have passed by the total number of days in the month, and then multiplying by 100. For example:

    =(TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1)/(EOMONTH(TODAY(),0)-DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1)*100

    This formula will give you the percentage of the month that has passed.

To calculate the percentage of the month left, you can simply subtract the percentage of the month that has passed from 100. For example:

=100-(TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1)/(EOMONTH(TODAY(),0)-DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1)*100
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default calculate percentage of month passed

Anita,

Try this formatted as a percentage

=DAY(TODAY())/DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0))

Mike

"Anita" wrote:

I want to know how many days have lapsed in a month, every month, as a
percentage. So far we are doing it week by week, ie after the first week has
gone we say that 25% of the month has gone. We need it to be more acurate.
The problem we are having is that each month has a different amount of days.
Is there a formula we can use for this and one for the percentage of the
month left?

Thank you

Anita

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default calculate percentage of month passed

=MONTH(A1)/DAY(DATE(YEAR(A1),MONTH(A1)+1,1)-1)

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Anita" wrote:

I want to know how many days have lapsed in a month, every month, as a
percentage. So far we are doing it week by week, ie after the first week has
gone we say that 25% of the month has gone. We need it to be more acurate.
The problem we are having is that each month has a different amount of days.
Is there a formula we can use for this and one for the percentage of the
month left?

Thank you

Anita

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default calculate percentage of month passed

On Wednesday, September 9, 2009 at 7:52:15 PM UTC+1, Mike H wrote:
Anita,

Try this formatted as a percentage

=DAY(TODAY())/DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0))

Mike

"Anita" wrote:

I want to know how many days have lapsed in a month, every month, as a
percentage. So far we are doing it week by week, ie after the first week has
gone we say that 25% of the month has gone. We need it to be more acurate.
The problem we are having is that each month has a different amount of days.
Is there a formula we can use for this and one for the percentage of the
month left?

Thank you

Anita


That worked great for me thanks


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default calculate percentage of month passed

On Wednesday, September 9, 2009 at 8:40:12 AM UTC-10, Anita wrote:
I want to know how many days have lapsed in a month, every month, as a
percentage. So far we are doing it week by week, ie after the first week has
gone we say that 25% of the month has gone. We need it to be more acurate.
The problem we are having is that each month has a different amount of days.
Is there a formula we can use for this and one for the percentage of the
month left?

Thank you

Anita


Try this formula ... may be simpler ...
=day(A1)/day(eomonth(a1,0))

On a similar vein, to determine the percentage remaining in a month ...
=1-day(A1)/day(eomonth(a1,0))

Chuck
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default calculate percentage of month passed

On Wednesday, September 9, 2009 at 1:52:15 PM UTC-5, Mike H wrote:
Anita,

Try this formatted as a percentage

=DAY(TODAY())/DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0))

Mike

"Anita" wrote:

I want to know how many days have lapsed in a month, every month, as a
percentage. So far we are doing it week by week, ie after the first week has
gone we say that 25% of the month has gone. We need it to be more acurate.
The problem we are having is that each month has a different amount of days.
Is there a formula we can use for this and one for the percentage of the
month left?

Thank you

Anita


HI Mike, Thank you for sharing. I'm in need of the same thing. How can it be linked to a date? One cell with the beginning date and one end date. Thank you
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
calculating percentage of year passed (for sales reports) morneauja Excel Worksheet Functions 1 April 22nd 09 11:59 PM
how can i calculate the number of days passed between 2 dates ente jozef carpenter Excel Discussion (Misc queries) 6 January 3rd 09 03:40 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
How do I calculate total of months that have passed? jaydubs Excel Discussion (Misc queries) 1 February 8th 05 12:27 PM


All times are GMT +1. The time now is 09:13 AM.

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"