If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
calculating number of three month periods between two dates...
Hi,
I've been working on a function to calculate how many quarters there are between two dates - but not the standard business quarters - effectively what I am trying to work out is the number of complete three month periods between two dates. Example: Start Date: 27/02/2006 End Date: 19/04/2013 I need to calculate how many complete three month periods have passed between the two dates - this is not as simple as converting the dates to a timestamp, defining a year as 60 * 60 * 24 * 365.25 seconds, then dividing by 4, as the length of months do differ, as henceforth quarters will differ. [while the above would work in a lot of cases, it would sometimes fall due to different lengths of months]. i.e. if you adjust the start dates: Quarter 1: 1 Feb - 30 April - 89 days Quarter 2: 1 May - 31 July - 92 days Therefore, I am trying to figure out how many entire three month periods has passed between the two dates. I'm sure this is no specific function that will do, but can it be done with a combination of other existing function - at the moment it looks like I'm going to have to write a VBA macro to parse the dates using lots of IF..ELSE statements. Also, a quarter (for this purpose) is defined as the first day in the period to the last day in the period. Therefore, 1 Jan - 31 March is a complete quarter (for tax purposes), not 1 Jan - 1 April. I tried the obvious line: =FLOOR((DATEDIF(STARTCELL,ENDCELL,"M")/3),1 Using the first set of dates, I get 0, and with the second set I get 1 It is close, but not what I actually am trying to do. Any suggestions greatly appreciated, as I'm getting quite stumped on this... Thanks Neil. |
#3
|
|||
|
|||
calculating number of three month periods between two dates...
Hi Neil
I think you need to do a couple of intermediate calculations to get adjusted start and end dates. I put start date in A2 and end date in B2. My formula for adjusted start date is in E2 =IF(DAY(A2)=1,A2,DATE(YEAR(A2),MONTH(A2)+1,1)) Formula for adjusted end date is in F2 =IF(DAY(B2)=DAY(DATE(YEAR(B2),MONTH(B2)+1,0)),B2,D ATE(YEAR(B2),MONTH(B2),0)) This ensures that we are dealing with complete months starting on day 1 of the month and ending on the appropriate last day of the month. Then using Datedif the answer for complete quarters is =INT((DATEDIF(E2,F2,"m")+1)/3) With your 3 sets of dates I get results of 28, 1 and 1 respectively. -- Regards Roger Govier "neil" wrote in message oups.com... Hi, I've been working on a function to calculate how many quarters there are between two dates - but not the standard business quarters - effectively what I am trying to work out is the number of complete three month periods between two dates. Example: Start Date: 27/02/2006 End Date: 19/04/2013 I need to calculate how many complete three month periods have passed between the two dates - this is not as simple as converting the dates to a timestamp, defining a year as 60 * 60 * 24 * 365.25 seconds, then dividing by 4, as the length of months do differ, as henceforth quarters will differ. [while the above would work in a lot of cases, it would sometimes fall due to different lengths of months]. i.e. if you adjust the start dates: Quarter 1: 1 Feb - 30 April - 89 days Quarter 2: 1 May - 31 July - 92 days Therefore, I am trying to figure out how many entire three month periods has passed between the two dates. I'm sure this is no specific function that will do, but can it be done with a combination of other existing function - at the moment it looks like I'm going to have to write a VBA macro to parse the dates using lots of IF..ELSE statements. Also, a quarter (for this purpose) is defined as the first day in the period to the last day in the period. Therefore, 1 Jan - 31 March is a complete quarter (for tax purposes), not 1 Jan - 1 April. I tried the obvious line: =FLOOR((DATEDIF(STARTCELL,ENDCELL,"M")/3),1 Using the first set of dates, I get 0, and with the second set I get 1 It is close, but not what I actually am trying to do. Any suggestions greatly appreciated, as I'm getting quite stumped on this... Thanks Neil. |
#4
|
|||
|
|||
calculating number of three month periods between two dates...
Thanks for the replies.
They work perfectly. Thanks Neil. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Microsoft Access Limits (file size, table records, users) | Mike | General Discussion | 4 | November 4th, 2005 03:01 AM |
Calculating the number of cells containing dates within the last m | louise | Worksheet Functions | 5 | October 29th, 2005 07:43 PM |
Calculating number of days between two dates that fall between two other dates | [email protected] | General Discussion | 5 | October 26th, 2005 06:18 PM |
Calculating Number of overdue dates with certain conditions | joannanpa | Worksheet Functions | 4 | January 19th, 2004 09:44 PM |
calculating number of days between two dates | sandy | Worksheet Functions | 3 | September 17th, 2003 04:23 AM |