Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Months within a specific period
Hi....
Just wondering if anyone could help me with a formula or suggest how I may do the following: I have a list of contracts with dates from and to and would like to calculate the number of months in each contract and which financial year they fall in (financial year running from 01/04 - 31/03) for example: Contract A: 01/01/08 - 01/06/09 this will have 3months in 07/08 12 in 08/09 and 2 in 09/10. Any help will be appreciated. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Months within a specific period
Hi,
I really don't understand you data or your questions. But here are some ideas If you want to know the number of "whole" months between two dates you can use =DATEDIF(A1,B1,"md") where the start date is in A1 and the end date is in B1. This gives no credit for partial months. You can find the Fiscal year by setting up a lookup table something like this 4/1/2008 2008 4/1/2009 2009 4/1/2010 2010 4/1/2011 2011 If this was in the cells H1:I4 then your formula would be =VLOOKUP(B1,H$1:I$4,2,TRUE) All this said, I don't understand how a contract that goes from 1/1/08 to 1/06/09 would have 3 months in 7/8? Assuming that 7/8 represents August 8 and you started on January 1, how do you get 3 months? Or any of the other figures? Well if any of this helps, please click the Yes button. -- Thanks, Shane Devenshire "Sarah" wrote: Hi.... Just wondering if anyone could help me with a formula or suggest how I may do the following: I have a list of contracts with dates from and to and would like to calculate the number of months in each contract and which financial year they fall in (financial year running from 01/04 - 31/03) for example: Contract A: 01/01/08 - 01/06/09 this will have 3months in 07/08 12 in 08/09 and 2 in 09/10. Any help will be appreciated. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Months within a specific period
Hi,
Thanks for your suggestion. When i say 07/08, 08/09 i mean the financial year they fall in meaning that the financial year of 07/08 runs from 01/04/2007 to the 31/03/2008 meaning that in the example i gave 3 months of the contract would of fallen in the financial year of 2007/2008 etc. "ShaneDevenshire" wrote: Hi, I really don't understand you data or your questions. But here are some ideas If you want to know the number of "whole" months between two dates you can use =DATEDIF(A1,B1,"md") where the start date is in A1 and the end date is in B1. This gives no credit for partial months. You can find the Fiscal year by setting up a lookup table something like this 4/1/2008 2008 4/1/2009 2009 4/1/2010 2010 4/1/2011 2011 If this was in the cells H1:I4 then your formula would be =VLOOKUP(B1,H$1:I$4,2,TRUE) All this said, I don't understand how a contract that goes from 1/1/08 to 1/06/09 would have 3 months in 7/8? Assuming that 7/8 represents August 8 and you started on January 1, how do you get 3 months? Or any of the other figures? Well if any of this helps, please click the Yes button. -- Thanks, Shane Devenshire "Sarah" wrote: Hi.... Just wondering if anyone could help me with a formula or suggest how I may do the following: I have a list of contracts with dates from and to and would like to calculate the number of months in each contract and which financial year they fall in (financial year running from 01/04 - 31/03) for example: Contract A: 01/01/08 - 01/06/09 this will have 3months in 07/08 12 in 08/09 and 2 in 09/10. Any help will be appreciated. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Months within a specific period
Hi,
I need to correct my previous formula =DATEDIF(A1,B1,"m") I entered "md" in my original formula, sorry. That is clearer. So now the question becomes suppose the contract starts on Apr 30 and today is May 1 There are three ways at least that you could calculate the number of months: 1. 0 months because no full month has passed 2. 1 month because April is finished but May is not finished 3. 2 months because we have at least 1 day in each of the two months. The Datedif function only counts a month is you have reached the same day of the month in the following month. If any of this helps, please click the Yes button. -- Thanks, Shane Devenshire "Sarah" wrote: Hi, Thanks for your suggestion. When i say 07/08, 08/09 i mean the financial year they fall in meaning that the financial year of 07/08 runs from 01/04/2007 to the 31/03/2008 meaning that in the example i gave 3 months of the contract would of fallen in the financial year of 2007/2008 etc. "ShaneDevenshire" wrote: Hi, I really don't understand you data or your questions. But here are some ideas If you want to know the number of "whole" months between two dates you can use =DATEDIF(A1,B1,"md") where the start date is in A1 and the end date is in B1. This gives no credit for partial months. You can find the Fiscal year by setting up a lookup table something like this 4/1/2008 2008 4/1/2009 2009 4/1/2010 2010 4/1/2011 2011 If this was in the cells H1:I4 then your formula would be =VLOOKUP(B1,H$1:I$4,2,TRUE) All this said, I don't understand how a contract that goes from 1/1/08 to 1/06/09 would have 3 months in 7/8? Assuming that 7/8 represents August 8 and you started on January 1, how do you get 3 months? Or any of the other figures? Well if any of this helps, please click the Yes button. -- Thanks, Shane Devenshire "Sarah" wrote: Hi.... Just wondering if anyone could help me with a formula or suggest how I may do the following: I have a list of contracts with dates from and to and would like to calculate the number of months in each contract and which financial year they fall in (financial year running from 01/04 - 31/03) for example: Contract A: 01/01/08 - 01/06/09 this will have 3months in 07/08 12 in 08/09 and 2 in 09/10. Any help will be appreciated. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Months within a specific period
With your Contract start date in A3, the Contract end date in B3 and 2007,
2008, 2009, 2010, 2011 etc in C1:F1 respectively then try: In C3 enter the formula: =IF($A$3<DATE(C1,4,1),DATEDIF($A$3,DATE(C1,4,1),"m "),"") then in D3: =IF(AND($B$3DATE(C1,4,1),$A$3<DATE(D1,4,1)),DATED IF($A$3,MIN($B$3,DATE(D1,4,1)),"m")-SUM($C$3:C3),"") and drag across as far as year dates are in Row 1 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sarah" wrote in message ... Hi.... Just wondering if anyone could help me with a formula or suggest how I may do the following: I have a list of contracts with dates from and to and would like to calculate the number of months in each contract and which financial year they fall in (financial year running from 01/04 - 31/03) for example: Contract A: 01/01/08 - 01/06/09 this will have 3months in 07/08 12 in 08/09 and 2 in 09/10. Any help will be appreciated. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Months within a specific period
Thats great thanks for your help
"ShaneDevenshire" wrote: Hi, I need to correct my previous formula =DATEDIF(A1,B1,"m") I entered "md" in my original formula, sorry. That is clearer. So now the question becomes suppose the contract starts on Apr 30 and today is May 1 There are three ways at least that you could calculate the number of months: 1. 0 months because no full month has passed 2. 1 month because April is finished but May is not finished 3. 2 months because we have at least 1 day in each of the two months. The Datedif function only counts a month is you have reached the same day of the month in the following month. If any of this helps, please click the Yes button. -- Thanks, Shane Devenshire "Sarah" wrote: Hi, Thanks for your suggestion. When i say 07/08, 08/09 i mean the financial year they fall in meaning that the financial year of 07/08 runs from 01/04/2007 to the 31/03/2008 meaning that in the example i gave 3 months of the contract would of fallen in the financial year of 2007/2008 etc. "ShaneDevenshire" wrote: Hi, I really don't understand you data or your questions. But here are some ideas If you want to know the number of "whole" months between two dates you can use =DATEDIF(A1,B1,"md") where the start date is in A1 and the end date is in B1. This gives no credit for partial months. You can find the Fiscal year by setting up a lookup table something like this 4/1/2008 2008 4/1/2009 2009 4/1/2010 2010 4/1/2011 2011 If this was in the cells H1:I4 then your formula would be =VLOOKUP(B1,H$1:I$4,2,TRUE) All this said, I don't understand how a contract that goes from 1/1/08 to 1/06/09 would have 3 months in 7/8? Assuming that 7/8 represents August 8 and you started on January 1, how do you get 3 months? Or any of the other figures? Well if any of this helps, please click the Yes button. -- Thanks, Shane Devenshire "Sarah" wrote: Hi.... Just wondering if anyone could help me with a formula or suggest how I may do the following: I have a list of contracts with dates from and to and would like to calculate the number of months in each contract and which financial year they fall in (financial year running from 01/04 - 31/03) for example: Contract A: 01/01/08 - 01/06/09 this will have 3months in 07/08 12 in 08/09 and 2 in 09/10. Any help will be appreciated. Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating Months within a specific period
Thanks for that thats great.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating months with half months. | Excel Discussion (Misc queries) | |||
Calculating time from a specific paycheck period | Excel Worksheet Functions | |||
Highest 12-Month Period Average Over Range of Months | Excel Worksheet Functions | |||
Loan period in Months | Excel Discussion (Misc queries) | |||
Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions |