Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculations based upon months
Hi,
In order to calculate an amount thru the past month, I refernce a cell that contains the number of months I am including. My calendar runs from July thru June. So, I have been diving the total by a cell that has the number of past months on my calendar (6) divided by 12. So my formaula looks like (=B18/($L$1/12)). Is there a way that I can reference that 6 out of the 12 months have passed? Any assistance would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculations based upon months
I'm trying to read between the lines here... Are you asking to calculate how
many FULL months have passed from July 1 through today? If so, should days in the month of July show as 0? If the answers are yes and yes, then =MOD(MONTH(TODAY()-DAY(TODAY())+1)-7,12) ought to do it. If you want to count the elapsed months through yesterday, change both instances of today() to today()-1. If you want dates in July to show as 12, I think you can change the ending from 7,12) to 8,12)+1. If my interpretation is off, please provide more complete requirements. "fascal" wrote: Hi, In order to calculate an amount thru the past month, I refernce a cell that contains the number of months I am including. My calendar runs from July thru June. So, I have been diving the total by a cell that has the number of past months on my calendar (6) divided by 12. So my formaula looks like (=B18/($L$1/12)). Is there a way that I can reference that 6 out of the 12 months have passed? Any assistance would be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculations based upon months
Thanks. That is almost it. The formula works great. Will I have to update
the formula monthly? "bpeltzer" wrote: I'm trying to read between the lines here... Are you asking to calculate how many FULL months have passed from July 1 through today? If so, should days in the month of July show as 0? If the answers are yes and yes, then =MOD(MONTH(TODAY()-DAY(TODAY())+1)-7,12) ought to do it. If you want to count the elapsed months through yesterday, change both instances of today() to today()-1. If you want dates in July to show as 12, I think you can change the ending from 7,12) to 8,12)+1. If my interpretation is off, please provide more complete requirements. "fascal" wrote: Hi, In order to calculate an amount thru the past month, I refernce a cell that contains the number of months I am including. My calendar runs from July thru June. So, I have been diving the total by a cell that has the number of past months on my calendar (6) divided by 12. So my formaula looks like (=B18/($L$1/12)). Is there a way that I can reference that 6 out of the 12 months have passed? Any assistance would be appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculations based upon months
No; if you had to update it monthly, there wouldn't be much point in
creating the formula. Everytime you open the file, the # of full months elapsed will be updated based on the system date (retrived by the today() function). "fascal" wrote: Thanks. That is almost it. The formula works great. Will I have to update the formula monthly? "bpeltzer" wrote: I'm trying to read between the lines here... Are you asking to calculate how many FULL months have passed from July 1 through today? If so, should days in the month of July show as 0? If the answers are yes and yes, then =MOD(MONTH(TODAY()-DAY(TODAY())+1)-7,12) ought to do it. If you want to count the elapsed months through yesterday, change both instances of today() to today()-1. If you want dates in July to show as 12, I think you can change the ending from 7,12) to 8,12)+1. If my interpretation is off, please provide more complete requirements. "fascal" wrote: Hi, In order to calculate an amount thru the past month, I refernce a cell that contains the number of months I am including. My calendar runs from July thru June. So, I have been diving the total by a cell that has the number of past months on my calendar (6) divided by 12. So my formaula looks like (=B18/($L$1/12)). Is there a way that I can reference that 6 out of the 12 months have passed? Any assistance would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum cells based on number of months | Excel Worksheet Functions | |||
Exclude $ Amount from sum based on how many months its been | Excel Worksheet Functions | |||
Sum based on two selections - months | Excel Discussion (Misc queries) | |||
Set an array on based on duration of months | Excel Worksheet Functions | |||
Calculations Based on Format | Excel Worksheet Functions |