Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Number of years, months, days between two dates.
Hello.
I have a function that allows me to calculate the length of time between two dates. =YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1), DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1) <=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<M ONTH(A1),DAY(B1) =DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)<D AY(A1)),-1)))&" months, "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days" I have a list of dates in adjacent columns. I want to be able to display the result of this function in a third column, for the two dates in each row. Can anyone tell me whether or not it is possible to somehow do this more easily than pasting the function into each cell of the 3rd column and changing the cell references in the funtion manually? This is not practical as the columns are too large! I only have a limited knowledge of Excel so I apologise if this is a simple query. Hope you clever people can help! Many thanks |
#2
|
|||
|
|||
Possibly this will work
=DATEDIF(A1,B1,"Y")&" Years "&DATEDIF(A1,B1,"YM")&" Months "&DATEDIF(A1,B1,"Md")&" Days" Startdate is in A1, Enddate is in B1 HTH PC "Bluenose" wrote in message ... Hello. I have a function that allows me to calculate the length of time between two dates. =YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1), DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1) <=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<M ONTH(A1),DAY(B1) =DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)<D AY(A1)),-1)))&" months, "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days" I have a list of dates in adjacent columns. I want to be able to display the result of this function in a third column, for the two dates in each row. Can anyone tell me whether or not it is possible to somehow do this more easily than pasting the function into each cell of the 3rd column and changing the cell references in the funtion manually? This is not practical as the columns are too large! I only have a limited knowledge of Excel so I apologise if this is a simple query. Hope you clever people can help! Many thanks |
#3
|
|||
|
|||
That will normally work, but note that DATEDIF assumes a month is as
long as the starting month (first argument), so if A1: 31 January 2005 A2: 1 March 2005 A3: =DATEDIF(A1,B1,"Y")&" Years "&DATEDIF(A1,B1,"YM")&" Months " & DATEDIF(A1,B1,"Md")&" Days" will return 0 Years 1 Months -2 Days There really isn't any consistent workaround, since "month" is not an exact unit. In article , "PC" wrote: Possibly this will work =DATEDIF(A1,B1,"Y")&" Years "&DATEDIF(A1,B1,"YM")&" Months "&DATEDIF(A1,B1,"Md")&" Days" Startdate is in A1, Enddate is in B1 |
#4
|
|||
|
|||
Thanks JE
Didn't realize that DATEDIF would "error" that easily. (working with dates is a huge pain) PC "JE McGimpsey" wrote in message ... That will normally work, but note that DATEDIF assumes a month is as long as the starting month (first argument), so if A1: 31 January 2005 A2: 1 March 2005 A3: =DATEDIF(A1,B1,"Y")&" Years "&DATEDIF(A1,B1,"YM")&" Months " & DATEDIF(A1,B1,"Md")&" Days" will return 0 Years 1 Months -2 Days There really isn't any consistent workaround, since "month" is not an exact unit. In article , "PC" wrote: Possibly this will work =DATEDIF(A1,B1,"Y")&" Years "&DATEDIF(A1,B1,"YM")&" Months "&DATEDIF(A1,B1,"Md")&" Days" Startdate is in A1, Enddate is in B1 |
#5
|
|||
|
|||
On Thu, 19 May 2005 15:57:03 -0700, "Bluenose"
wrote: Hello. I have a function that allows me to calculate the length of time between two dates. =YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1), DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1) <=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)< MONTH(A1),DAY(B1) =DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)< DAY(A1)),-1)))&" months, "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days" I have a list of dates in adjacent columns. I want to be able to display the result of this function in a third column, for the two dates in each row. Can anyone tell me whether or not it is possible to somehow do this more easily than pasting the function into each cell of the 3rd column and changing the cell references in the funtion manually? This is not practical as the columns are too large! I only have a limited knowledge of Excel so I apologise if this is a simple query. Hope you clever people can help! Many thanks Any result expressed in years, months and days will be inexact since a "month" can be anywhere from 28-31 days. So is some rough approximation OK, or do you want to set up rules for what to do if, for example, your dates are 31 Jan 2005; 1 Mar 2005. Both your formula and the DATEDIF() formula posted by PC give a result of 0 years, 1 months, -2 days --ron |
#6
|
|||
|
|||
Hi
"JE McGimpsey" wrote in message ... That will normally work, but note that DATEDIF assumes a month is as long as the starting month (first argument), so if A1: 31 January 2005 A2: 1 March 2005 A3: =DATEDIF(A1,B1,"Y")&" Years "&DATEDIF(A1,B1,"YM")&" Months " & DATEDIF(A1,B1,"Md")&" Days" will return 0 Years 1 Months -2 Days There really isn't any consistent workaround, since "month" is not an exact unit. This was a bad surprise for me - I have used DATEDIF quite often, and as I now see, without checking it tgroughly before! How about this workaround (days part only): =DATEDIF(A1,B1,"MD")+(DAY(A1)DAY(B1))*MAX(0,DAY(E OMONTH(A1,0))-DAY(EOMONTH(B1,-1))) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) In article , "PC" wrote: Possibly this will work =DATEDIF(A1,B1,"Y")&" Years "&DATEDIF(A1,B1,"YM")&" Months "&DATEDIF(A1,B1,"Md")&" Days" Startdate is in A1, Enddate is in B1 |
#7
|
|||
|
|||
=YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1) & " " &
(MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0) & " " & IF(DAY(A2)-DAY(A1)<0,DAY(DATE(YEAR(A2),MONTH(A2),1)-1)+(DAY(A2)-DAY(A1)),DAY (A2)-DAY(A1)) A1 = start date A2 = End date This should work fine enough. Tested 4 cases with it: http://excelforum.com/showthread.php?t=371874 - Mangesh "Ron Rosenfeld" wrote in message ... On Thu, 19 May 2005 15:57:03 -0700, "Bluenose" wrote: Hello. I have a function that allows me to calculate the length of time between two dates. =YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1), DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1) <=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)< MONTH(A1),DAY(B1) =DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)< DAY(A1)),-1)))&" months, "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days" I have a list of dates in adjacent columns. I want to be able to display the result of this function in a third column, for the two dates in each row. Can anyone tell me whether or not it is possible to somehow do this more easily than pasting the function into each cell of the 3rd column and changing the cell references in the funtion manually? This is not practical as the columns are too large! I only have a limited knowledge of Excel so I apologise if this is a simple query. Hope you clever people can help! Many thanks Any result expressed in years, months and days will be inexact since a "month" can be anywhere from 28-31 days. So is some rough approximation OK, or do you want to set up rules for what to do if, for example, your dates are 31 Jan 2005; 1 Mar 2005. Both your formula and the DATEDIF() formula posted by PC give a result of 0 years, 1 months, -2 days --ron |
#8
|
|||
|
|||
You simply need to drag down (copy) your result in subsequent rows below. No
need to edit each time. - Mangesh "Bluenose" wrote in message ... Hello. I have a function that allows me to calculate the length of time between two dates. =YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1), DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1) <=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<M ONTH(A1),DAY(B1) =DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)<D AY(A1)),-1)))&" months, "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days" I have a list of dates in adjacent columns. I want to be able to display the result of this function in a third column, for the two dates in each row. Can anyone tell me whether or not it is possible to somehow do this more easily than pasting the function into each cell of the 3rd column and changing the cell references in the funtion manually? This is not practical as the columns are too large! I only have a limited knowledge of Excel so I apologise if this is a simple query. Hope you clever people can help! Many thanks |
#9
|
|||
|
|||
On Fri, 20 May 2005 11:44:13 +0530, "Mangesh"
wrote: =YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1) & " " & (MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0) & " " & IF(DAY(A2)-DAY(A1)<0,DAY(DATE(YEAR(A2),MONTH(A2),1)-1)+(DAY(A2)-DAY(A1)),DAY (A2)-DAY(A1)) A1 = start date A2 = End date It gives silly answer with A1: 31 Jan 2005 A2: 1 Mar 2005 --ron |
#10
|
|||
|
|||
Perhaps, but it gives some inconsistent answers...
A1: 12/30/2004 B1: 4/30/2004 === 0 Add 1 day: B1: 5/1/2004 === 2 What happened to 1? Another: A1: 12/30/2004 A2: 2/28/2005 === 29 but A2: 3/1/2005 === 2 Now we skip 0 and 1. Those may be acceptable results for some circumstances, but it probably isn't for others. The problem, I think, is intractable. What is *exactly* 12/30/2004 plus two months? Legitimate cases can be made for any day in the range 2/27/2005 - 3/2/2005, depending on how you define "month". In article , "Arvi Laanemets" wrote: This was a bad surprise for me - I have used DATEDIF quite often, and as I now see, without checking it tgroughly before! How about this workaround (days part only): =DATEDIF(A1,B1,"MD")+(DAY(A1)DAY(B1))*MAX(0,DAY(E OMONTH(A1,0))-DAY(EOMONTH(B1 ,-1))) |
#11
|
|||
|
|||
Once the period of time between the two dates is calculated, is there any way
to allocate a certian amount of money over that period in terms of straight line depreciation. For example: If the cost of an asset is $1000 and it is depreciated over a 10 year period, the asset would be depreciated $100/year ($1,000/10years=$100/year;salvage value being ignored). So basically...once the annual depreciation is determined ($100 for the above example), can I take the DATEDIF cell and multiply it by the annual depreciation for the asset...taking into account years, months, and days? Thanks for any help. Peter "Arvi Laanemets" wrote: Hi "JE McGimpsey" wrote in message ... That will normally work, but note that DATEDIF assumes a month is as long as the starting month (first argument), so if A1: 31 January 2005 A2: 1 March 2005 A3: =DATEDIF(A1,B1,"Y")&" Years "&DATEDIF(A1,B1,"YM")&" Months " & DATEDIF(A1,B1,"Md")&" Days" will return 0 Years 1 Months -2 Days There really isn't any consistent workaround, since "month" is not an exact unit. This was a bad surprise for me - I have used DATEDIF quite often, and as I now see, without checking it tgroughly before! How about this workaround (days part only): =DATEDIF(A1,B1,"MD")+(DAY(A1)DAY(B1))*MAX(0,DAY(E OMONTH(A1,0))-DAY(EOMONTH(B1,-1))) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) In article , "PC" wrote: Possibly this will work =DATEDIF(A1,B1,"Y")&" Years "&DATEDIF(A1,B1,"YM")&" Months "&DATEDIF(A1,B1,"Md")&" Days" Startdate is in A1, Enddate is in B1 |
#12
|
|||
|
|||
That would be a rather backward way of doing things, since your DATEDIF
functions were being concatenated into a text string. If A1 is your inservice date, and B1 is your period date, and you really need exact daily depreciation (though I wouldn't know why), I'd be more inclined to use =(B1-A1)*SLN(1000,0,DATE(YEAR(A1)+10,MONTH(A1),DAY(A1))-A1) (using SLN allows you to take salvage into account). You'd be a little less accurate with =(B1-A1)*SLN(1000, 0, 3652.5) depending on the timing of leap years - you may be off by a dime or so at any one point. In article , Peter Burkes <Peter wrote: Once the period of time between the two dates is calculated, is there any way to allocate a certian amount of money over that period in terms of straight line depreciation. For example: If the cost of an asset is $1000 and it is depreciated over a 10 year period, the asset would be depreciated $100/year ($1,000/10years=$100/year;salvage value being ignored). So basically...once the annual depreciation is determined ($100 for the above example), can I take the DATEDIF cell and multiply it by the annual depreciation for the asset...taking into account years, months, and days? |
#13
|
|||
|
|||
=YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1) & " " &
(MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0) & " " & A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1)),MONTH(A1) +((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0 )),DAY(A1)))+IF(A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1) ,0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2 )<MONTH(A1),12,0)),DAY(A1)))<0,DAY(DATE(YEAR(A1)+( YEAR(A2)-YEAR(A1)-IF(MONTH (A2)=MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0 ,1)+IF(MONTH(A2)<MONTH(A1),12,0)),DAY(A1))),0) - Mangesh "Ron Rosenfeld" wrote in message ... On Fri, 20 May 2005 11:44:13 +0530, "Mangesh" wrote: =YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1) & " " & (MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0) & " " & IF(DAY(A2)-DAY(A1)<0,DAY(DATE(YEAR(A2),MONTH(A2),1)-1)+(DAY(A2)-DAY(A1)),DA Y (A2)-DAY(A1)) A1 = start date A2 = End date It gives silly answer with A1: 31 Jan 2005 A2: 1 Mar 2005 --ron |
#14
|
|||
|
|||
Hmmmm...
A1: 12/30/2004 A2: 3/1/2005 === 0 2 1 Add one day: A2: 3/2/2005 === 0 2 0 Add another day: A2: 3/3/2005 === 0 2 1 or A2: 12/1/2005 === 1 -1 1 In article , "Mangesh" wrote: =YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1) & " " & (MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0) & " " & A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1)),MONTH(A1) +((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0 )),DAY(A1)))+IF(A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1) ,0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2 )<MONTH(A1),12,0)),DAY(A1)))<0,DAY(DATE(YEAR(A1)+( YEAR(A2)-YEAR(A1)-IF(MONTH (A2)=MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0 ,1)+IF(MONTH(A2)<MONTH(A1),12,0)),DAY(A1))),0) |
#15
|
|||
|
|||
=YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1) & " " &
(MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0) & " " & IF(DAY((DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1)),MONTH (A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1), 12,0)),DAY(A1))))=DAY(A1),A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2) =MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+I F(MONTH(A2)<MONTH(A1),12,0)),DAY(A1))),DAY(DATE(YE AR(A1),MONTH(A1)+1,1)-1)-D AY(A1)+DAY(A2)) should solve that problem. Mangesh "JE McGimpsey" wrote in message ... Hmmmm... A1: 12/30/2004 A2: 3/1/2005 === 0 2 1 Add one day: A2: 3/2/2005 === 0 2 0 Add another day: A2: 3/3/2005 === 0 2 1 or A2: 12/1/2005 === 1 -1 1 In article , "Mangesh" wrote: =YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1) & " " & (MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0) & " " & A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1)),MONTH(A1) +((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0 )),DAY(A1)))+IF(A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A 1) ,0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2 )<MONTH(A1),12,0)),DAY(A1)))<0,DAY(DATE(YEAR(A1)+( YEAR(A2)-YEAR(A1)-IF(MON TH (A2)=MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0 ,1)+IF(MONTH(A2)<MONTH(A1),12,0)),DAY(A1))),0) |
#16
|
|||
|
|||
1 problem - month still left
Mangesh "Mangesh" wrote in message ... =YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1) & " " & (MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0) & " " & IF(DAY((DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1)),MONTH (A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1), 12,0)),DAY(A1))))=DAY(A1),A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2) =MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+I F(MONTH(A2)<MONTH(A1),12,0)),DAY(A1))),DAY(DATE(YE AR(A1),MONTH(A1)+1,1)-1)-D AY(A1)+DAY(A2)) should solve that problem. Mangesh "JE McGimpsey" wrote in message ... Hmmmm... A1: 12/30/2004 A2: 3/1/2005 === 0 2 1 Add one day: A2: 3/2/2005 === 0 2 0 Add another day: A2: 3/3/2005 === 0 2 1 or A2: 12/1/2005 === 1 -1 1 In article , "Mangesh" wrote: =YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1) & " " & (MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0) & " " & A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1)),MONTH(A1) +((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0 )),DAY(A1)))+IF(A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A 1) ,0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2 )<MONTH(A1),12,0)),DAY(A1)))<0,DAY(DATE(YEAR(A1)+( YEAR(A2)-YEAR(A1)-IF(MON TH (A2)=MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0 ,1)+IF(MONTH(A2)<MONTH(A1),12,0)),DAY(A1))),0) |
#17
|
|||
|
|||
This should solve the negative month problem as well.
=YEAR(M2)-YEAR(M1)-IF(MONTH(M2)MONTH(M1),0,1) & " " & (MONTH(M2)-MONTH(M1))-IF(DAY(M2)=DAY(M1),0,1)+IF(MONTH(M2)<=MONTH(M1),1 2,0) & " " & IF(DAY((DATE(YEAR(M1)+(YEAR(M2)-YEAR(M1)-IF(MONTH(M2)=MONTH(M1),0,1)),MONTH (M1)+((MONTH(M2)-MONTH(M1))-IF(DAY(M2)=DAY(M1),0,1)+IF(MONTH(M2)<MONTH(M1), 12,0)),DAY(M1))))=DAY(M1),M2-(DATE(YEAR(M1)+(YEAR(M2)-YEAR(M1)-IF(MONTH(M2) =MONTH(M1),0,1)),MONTH(M1)+((MONTH(M2)-MONTH(M1))-IF(DAY(M2)=DAY(M1),0,1)+I F(MONTH(M2)<MONTH(M1),12,0)),DAY(M1))),DAY(DATE(YE AR(M1),MONTH(M1)+1,1)-1)-D AY(M1)+DAY(M2)) - Mangesh "Mangesh" wrote in message ... =YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1) & " " & (MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0) & " " & IF(DAY((DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1)),MONTH (A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1), 12,0)),DAY(A1))))=DAY(A1),A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2) =MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+I F(MONTH(A2)<MONTH(A1),12,0)),DAY(A1))),DAY(DATE(YE AR(A1),MONTH(A1)+1,1)-1)-D AY(A1)+DAY(A2)) should solve that problem. Mangesh "JE McGimpsey" wrote in message ... Hmmmm... A1: 12/30/2004 A2: 3/1/2005 === 0 2 1 Add one day: A2: 3/2/2005 === 0 2 0 Add another day: A2: 3/3/2005 === 0 2 1 or A2: 12/1/2005 === 1 -1 1 In article , "Mangesh" wrote: =YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1) & " " & (MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0) & " " & A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A1),0,1)),MONTH(A1) +((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2)<MONTH(A1),12 ,0 )),DAY(A1)))+IF(A2-(DATE(YEAR(A1)+(YEAR(A2)-YEAR(A1)-IF(MONTH(A2)=MONTH(A 1) ,0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0,1)+IF(MONTH(A2 )<MONTH(A1),12,0)),DAY(A1)))<0,DAY(DATE(YEAR(A1)+( YEAR(A2)-YEAR(A1)-IF(MON TH (A2)=MONTH(A1),0,1)),MONTH(A1)+((MONTH(A2)-MONTH(A1))-IF(DAY(A2)=DAY(A1),0 ,1)+IF(MONTH(A2)<MONTH(A1),12,0)),DAY(A1))),0) |
#18
|
|||
|
|||
On Sat, 21 May 2005 11:50:31 +0530, "Mangesh"
wrote: This should solve the negative month problem as well. =YEAR(M2)-YEAR(M1)-IF(MONTH(M2)MONTH(M1),0,1) & " " & (MONTH(M2)-MONTH(M1))-IF(DAY(M2)=DAY(M1),0,1)+IF(MONTH(M2)<=MONTH(M1),1 2,0) & " " & IF(DAY((DATE(YEAR(M1)+(YEAR(M2)-YEAR(M1)-IF(MONTH(M2)=MONTH(M1),0,1)),MONTH (M1)+((MONTH(M2)-MONTH(M1))-IF(DAY(M2)=DAY(M1),0,1)+IF(MONTH(M2)<MONTH(M1), 12,0)),DAY(M1))))=DAY(M1),M2-(DATE(YEAR(M1)+(YEAR(M2)-YEAR(M1)-IF(MONTH(M2) =MONTH(M1),0,1)),MONTH(M1)+((MONTH(M2)-MONTH(M1))-IF(DAY(M2)=DAY(M1),0,1)+I F(MONTH(M2)<MONTH(M1),12,0)),DAY(M1))),DAY(DATE(Y EAR(M1),MONTH(M1)+1,1)-1)-D AY(M1)+DAY(M2)) It may solve the negative month problem, but given: 28 Feb 2005 1 Jan 2006 I would count 10 full months (Mar-Dec) plus one or two days (depending on whether one is including or excluding the bounds. Your formula gives a result of 10 months 4 days. Also: 1 Jan 2005 2 Jan 2005 Your formula gives: -1 12 1 But consider also the issue of what should be the answer given dates: 27 Feb 2005 28 Mar 2005 Is the "best" answer 1 month, 27 days, or 28 days? My point remains that dealing with months as a time interval is inherently confusing, unless the rules for using months are very clearly stated. And even then there will be some results that can only be explained by clearly understanding the stated rules. --ron |
#19
|
|||
|
|||
Hi Ron,
thanks for the bug. The following should sort it out. =(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)MONTH(B1),AN D(MON TH(A1)=MONTH(B1),DAY(A1)DAY(B1)))),1,0) & " " & (MONTH(B1)-MONTH(A1))-IF(AND(MONTH(A1)<MONTH(B1),DAY(A1)DAY(B1)),1,0)+I F(AN D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<=D AY(B1)),12,0)+IF(AND(YEAR( A1)<YEAR(B1),MONTH(A1)=MONTH(B1),DAY(A1)DAY(B1)) ,11,0) & " " & IF(DAY(B1)=DAY(A1),DAY(B1)-DAY(A1),IF(DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY (A1)<0,0,DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY(A1))+DAY(B1)) A1 is start date, and B1 is end date. Rules: 1. Exclude the start date, and include the end date; as excel does in its own calculations. For e.g. 23/05/2005 - 22/05/2005 = 1 day 2. As for your other 2 queries: 28 Feb 2005 to 1 Jan 2006 27 Feb 2005 to 28 Mar 2005 Here, the year and month is completed on the same 'day' (28 of start month) of the previous month (dec 2005) of the end day (jan 2006). So the new start date for the calculation of the number of days only is 28 dec 2005. From here on the remaining days are calculated. If this new start date falls on say 30 feb (error intended after the above calculation), then it is rounded down to a valid end date like 28 feb for non-leap years and 29 feb for leap. Comments awaited.... Regards Mangesh "Ron Rosenfeld" wrote in message ... On Sat, 21 May 2005 11:50:31 +0530, "Mangesh" wrote: This should solve the negative month problem as well. =YEAR(M2)-YEAR(M1)-IF(MONTH(M2)MONTH(M1),0,1) & " " & (MONTH(M2)-MONTH(M1))-IF(DAY(M2)=DAY(M1),0,1)+IF(MONTH(M2)<=MONTH(M1),1 2,0 ) & " " & IF(DAY((DATE(YEAR(M1)+(YEAR(M2)-YEAR(M1)-IF(MONTH(M2)=MONTH(M1),0,1)),MONT H (M1)+((MONTH(M2)-MONTH(M1))-IF(DAY(M2)=DAY(M1),0,1)+IF(MONTH(M2)<MONTH(M1) , 12,0)),DAY(M1))))=DAY(M1),M2-(DATE(YEAR(M1)+(YEAR(M2)-YEAR(M1)-IF(MONTH(M2) =MONTH(M1),0,1)),MONTH(M1)+((MONTH(M2)-MONTH(M1))-IF(DAY(M2)=DAY(M1),0,1)+ I F(MONTH(M2)<MONTH(M1),12,0)),DAY(M1))),DAY(DATE(Y EAR(M1),MONTH(M1)+1,1)-1)- D AY(M1)+DAY(M2)) It may solve the negative month problem, but given: 28 Feb 2005 1 Jan 2006 I would count 10 full months (Mar-Dec) plus one or two days (depending on whether one is including or excluding the bounds. Your formula gives a result of 10 months 4 days. Also: 1 Jan 2005 2 Jan 2005 Your formula gives: -1 12 1 But consider also the issue of what should be the answer given dates: 27 Feb 2005 28 Mar 2005 Is the "best" answer 1 month, 27 days, or 28 days? My point remains that dealing with months as a time interval is inherently confusing, unless the rules for using months are very clearly stated. And even then there will be some results that can only be explained by clearly understanding the stated rules. --ron |
#20
|
|||
|
|||
On Mon, 23 May 2005 12:15:21 +0530, "Mangesh"
wrote: Rules: 1. Exclude the start date, and include the end date; as excel does in its own calculations. For e.g. 23/05/2005 - 22/05/2005 = 1 day 2. As for your other 2 queries: 28 Feb 2005 to 1 Jan 2006 27 Feb 2005 to 28 Mar 2005 Here, the year and month is completed on the same 'day' (28 of start month) of the previous month (dec 2005) of the end day (jan 2006). So the new start date for the calculation of the number of days only is 28 dec 2005. From here on the remaining days are calculated. If this new start date falls on say 30 feb (error intended after the above calculation), then it is rounded down to a valid end date like 28 feb for non-leap years and 29 feb for leap. Comments awaited.... Your formula does seem to follow your rules. But those are not rules that I would use <g. They give rise to the (to me) illogical results with regard to the month of February. For example: 31 Jan 05 -- 28 Feb 05 to me encompasses a full month; as does 28 Feb 05 -- 31 Mar 05 (not counting Day 1). Yet the first gives a result of 0 0 28; and the second 0 1 3. --------------------- If I were using "months" in a count, I think I would devise an algorithm that would count full calendar months; followed by the number of days that is outside that range. So both of my examples above would result in "1 month". I would either accept that the days out of range might total to more than 31, or arbitrarily set 30 as the number of days in that pseudo-month. Another alternative would be to arbitrarily assume 30 day months and 360 day years. Which is what financial institutions did, and some probably still do. And I'm sure others would find illogical results with my "rules" :-) --ron |
#21
|
|||
|
|||
This seems to be self-consistent.
However, it's strange to me that one can't get a result of of 0 1 0, 0 3 0, 0 5 0, 0 8 0, or 0 10 0 from the date 1/31/2005. How do you go from 2/28/2005 being less than a month to 3/1/2005 being 1 day more than a month, when one never had a date that was a month later? In article , "Mangesh" wrote: thanks for the bug. The following should sort it out. =(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)MONTH(B1),AN D(MON TH(A1)=MONTH(B1),DAY(A1)DAY(B1)))),1,0) & " " & (MONTH(B1)-MONTH(A1))-IF(AND(MONTH(A1)<MONTH(B1),DAY(A1)DAY(B1)),1,0)+I F(AN D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<=D AY(B1)),12,0)+IF(AND(YEAR( A1)<YEAR(B1),MONTH(A1)=MONTH(B1),DAY(A1)DAY(B1)) ,11,0) & " " & IF(DAY(B1)=DAY(A1),DAY(B1)-DAY(A1),IF(DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY (A1)<0,0,DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY(A1))+DAY(B1)) A1 is start date, and B1 is end date. Rules: 1. Exclude the start date, and include the end date; as excel does in its own calculations. For e.g. 23/05/2005 - 22/05/2005 = 1 day 2. As for your other 2 queries: 28 Feb 2005 to 1 Jan 2006 27 Feb 2005 to 28 Mar 2005 Here, the year and month is completed on the same 'day' (28 of start month) of the previous month (dec 2005) of the end day (jan 2006). So the new start date for the calculation of the number of days only is 28 dec 2005. From here on the remaining days are calculated. If this new start date falls on say 30 feb (error intended after the above calculation), then it is rounded down to a valid end date like 28 feb for non-leap years and 29 feb for leap. Comments awaited.... |
#22
|
|||
|
|||
Hi Ron,
Thanks for pointing out the bug yet again. This should sort it out again. =(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)MONTH(B1),AN D(MON TH(A1)=MONTH(B1),DAY(A1)DAY(B1)))),1,0) & " " & (MONTH(B1)-MONTH(A1))-IF(AND(MONTH(A1)<MONTH(B1),DAY(A1)DAY(B1)),1,0)+I F(AN D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<=D AY(B1)),12,0)+IF(AND(YEAR( A1)<YEAR(B1),MONTH(A1)=MONTH(B1),DAY(A1)DAY(B1)) ,11,0)+IF(AND(MONTH(A1)<M ONTH(A1+1),MONTH(B1)<MONTH(B1+1),DAY(A1)DAY(B1)) ,1,0) & " " & IF(DAY(B1)=DAY(A1),DAY(B1)-DAY(A1),IF(DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY (A1)<0,0,DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY(A1))+DAY(B1))-IF(AND(MONTH(A1 )<MONTH(A1+1),MONTH(B1)<MONTH(B1+1),DAY(A1)DAY( B1)),DAY(B1),0) A1 = start date B1 = end date I agree with the first one and it should have been 0 1 0 and not 0 0 28. (Atleast thats my intention). As for the second one, I consider a month to get over on the same date next month, so it is as intended. Just saw the post by JE. <QUOTE This seems to be self-consistent. However, it's strange to me that one can't get a result of of 0 1 0, 0 3 0, 0 5 0, 0 8 0, or 0 10 0 from the date 1/31/2005. How do you go from 2/28/2005 being less than a month to 3/1/2005 being 1 day more than a month, when one never had a date that was a month later? </QUOTE There was a bug and this one should sort it out. Regards Mangesh Your formula does seem to follow your rules. But those are not rules that I would use <g. They give rise to the (to me) illogical results with regard to the month of February. For example: 31 Jan 05 -- 28 Feb 05 to me encompasses a full month; as does 28 Feb 05 -- 31 Mar 05 (not counting Day 1). Yet the first gives a result of 0 0 28; and the second 0 1 3. --------------------- If I were using "months" in a count, I think I would devise an algorithm that would count full calendar months; followed by the number of days that is outside that range. So both of my examples above would result in "1 month". I would either accept that the days out of range might total to more than 31, or arbitrarily set 30 as the number of days in that pseudo-month. Another alternative would be to arbitrarily assume 30 day months and 360 day years. Which is what financial institutions did, and some probably still do. And I'm sure others would find illogical results with my "rules" :-) --ron |
#23
|
|||
|
|||
Hmmm... no longer self-consistent:
A B C 1 1/28/2005 2/28/2005 0 1 0 2 1/29/2005 2/28/2005 0 0 30 3 1/30/2005 2/28/2005 0 0 29 4 1/31/2005 2/28/2005 0 1 0 5 2/01/2005 2/28/2005 0 0 27 In article , "Mangesh" wrote: Thanks for pointing out the bug yet again. This should sort it out again. =(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)MONTH(B1),AN D(MON TH(A1)=MONTH(B1),DAY(A1)DAY(B1)))),1,0) & " " & (MONTH(B1)-MONTH(A1))-IF(AND(MONTH(A1)<MONTH(B1),DAY(A1)DAY(B1)),1,0)+I F(AN D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<=D AY(B1)),12,0)+IF(AND(YEAR( A1)<YEAR(B1),MONTH(A1)=MONTH(B1),DAY(A1)DAY(B1)) ,11,0)+IF(AND(MONTH(A1)<M ONTH(A1+1),MONTH(B1)<MONTH(B1+1),DAY(A1)DAY(B1)) ,1,0) & " " & IF(DAY(B1)=DAY(A1),DAY(B1)-DAY(A1),IF(DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY (A1)<0,0,DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY(A1))+DAY(B1))-IF(AND(MONTH(A1 )<MONTH(A1+1),MONTH(B1)<MONTH(B1+1),DAY(A1)DAY( B1)),DAY(B1),0) A1 = start date B1 = end date I agree with the first one and it should have been 0 1 0 and not 0 0 28. (Atleast thats my intention). As for the second one, I consider a month to get over on the same date next month, so it is as intended. Just saw the post by JE. <QUOTE This seems to be self-consistent. However, it's strange to me that one can't get a result of of 0 1 0, 0 3 0, 0 5 0, 0 8 0, or 0 10 0 from the date 1/31/2005. How do you go from 2/28/2005 being less than a month to 3/1/2005 being 1 day more than a month, when one never had a date that was a month later? </QUOTE There was a bug and this one should sort it out. |
#24
|
|||
|
|||
On Mon, 23 May 2005 12:15:21 +0530, "Mangesh"
wrote: Hi Ron, thanks for the bug. The following should sort it out. =(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)MONTH(B1),AN D(MON TH(A1)=MONTH(B1),DAY(A1)DAY(B1)))),1,0) & " " & (MONTH(B1)-MONTH(A1))-IF(AND(MONTH(A1)<MONTH(B1),DAY(A1)DAY(B1)),1,0)+I F(AN D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<= DAY(B1)),12,0)+IF(AND(YEAR( A1)<YEAR(B1),MONTH(A1)=MONTH(B1),DAY(A1)DAY(B1) ),11,0) & " " & IF(DAY(B1)=DAY(A1),DAY(B1)-DAY(A1),IF(DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY (A1)<0,0,DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY(A1))+DAY(B1)) A1 is start date, and B1 is end date. Rules: 1. Exclude the start date, and include the end date; as excel does in its own calculations. For e.g. 23/05/2005 - 22/05/2005 = 1 day 2. As for your other 2 queries: 28 Feb 2005 to 1 Jan 2006 27 Feb 2005 to 28 Mar 2005 Here, the year and month is completed on the same 'day' (28 of start month) of the previous month (dec 2005) of the end day (jan 2006). So the new start date for the calculation of the number of days only is 28 dec 2005. From here on the remaining days are calculated. If this new start date falls on say 30 feb (error intended after the above calculation), then it is rounded down to a valid end date like 28 feb for non-leap years and 29 feb for leap. Comments awaited.... I think I may have come across an "illogicality" in your formula. StartDate: 12/31/2004 End Date: 4/30/2004 (or any similar sequence where the ending month ends prior to the beginning bonth). To my way of thinking, this should give a result of 4 months, exactly. Your formula gives 3 months 30 days By the way, for interest, here is a UDF I cobbled together to do it the way *I* would want to see it. In other words, Calendar Months (full months) plus add on the extra days at the beginning and end. This can leave one with a result of 1/1/2005 4/29/2005 0 yrs 2 months 59 days Since the only full months in this sequence are February and March. As written, the UDF also requires a reference to the ATP, but the eomonth function could certainly be rewritten in VBA. =================================== Function CalendarMonthsAndDays(d1 As Date, d2 As Date) As String Dim temp As Date Dim i As Double Dim yr As Long, mnth As Long, dy As Long Do Until temp = d2 i = i + 1 temp = eomonth(d1, i) Loop If temp < d2 Then i = i - 1 End If yr = Int(i / 12) mnth = i Mod 12 dy = d2 - eomonth(d1, i) + (eomonth(d1, 0) - d1) CalendarMonthsAndDays = yr & " yrs " & mnth & " months " & dy & " days" End Function ================================== --ron |
#25
|
|||
|
|||
Hi JE, Ron,
Ok. another minor adjustment: =(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)MONTH(B1),AN D(MON TH(A1)=MONTH(B1),DAY(A1)DAY(B1)))),1,0) & " " & (MONTH(B1)-MONTH(A1))-IF(AND(MONTH(A1)<MONTH(B1),DAY(A1)DAY(B1)),1,0)+I F(AN D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<=D AY(B1)),12,0)+IF(AND(YEAR( A1)<YEAR(B1),MONTH(A1)=MONTH(B1),DAY(A1)DAY(B1)) ,11,0)+IF(AND(MONTH(B1)<M ONTH(B1+1),DAY(A1)DAY(B1)),1,0) & " " & IF(DAY(B1)=DAY(A1),DAY(B1)-DAY(A1),IF(DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY (A1)<0,0,DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY(A1))+DAY(B1))-IF(AND(MONTH(B1 )<MONTH(B1+1),DAY(A1)DAY(B1)),DAY(B1)+DAY(DATE(Y EAR(A1),MONTH(A1)+1,1)-1)- DAY(A1),0) A1 = start date B1 = end date Ron, my whole idea at the moment is to avoid using any VBA and the analysis toolpak. And thats why I am trying to work my way through simple if conditions. Mangesh "JE McGimpsey" wrote in message ... Hmmm... no longer self-consistent: A B C 1 1/28/2005 2/28/2005 0 1 0 2 1/29/2005 2/28/2005 0 0 30 3 1/30/2005 2/28/2005 0 0 29 4 1/31/2005 2/28/2005 0 1 0 5 2/01/2005 2/28/2005 0 0 27 |
#26
|
|||
|
|||
Hate to be continually sniping, but
A B C 1 11/30/2005 02/28/2005 0 3 1 2 11/30/2005 03/01/2005 0 3 1 In article , "Mangesh" wrote: Hi JE, Ron, Ok. another minor adjustment: =(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)MONTH(B1),AN D(MON TH(A1)=MONTH(B1),DAY(A1)DAY(B1)))),1,0) & " " & (MONTH(B1)-MONTH(A1))-IF(AND(MONTH(A1)<MONTH(B1),DAY(A1)DAY(B1)),1,0)+I F(AN D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<=D AY(B1)),12,0)+IF(AND(YEAR( A1)<YEAR(B1),MONTH(A1)=MONTH(B1),DAY(A1)DAY(B1)) ,11,0)+IF(AND(MONTH(B1)<M ONTH(B1+1),DAY(A1)DAY(B1)),1,0) & " " & IF(DAY(B1)=DAY(A1),DAY(B1)-DAY(A1),IF(DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY (A1)<0,0,DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY(A1))+DAY(B1))-IF(AND(MONTH(B1 )<MONTH(B1+1),DAY(A1)DAY(B1)),DAY(B1)+DAY(DATE(Y EAR(A1),MONTH(A1)+1,1)-1)- DAY(A1),0) |
#27
|
|||
|
|||
Hi JE,
no problem. But I forgot to mention that the start date has to be less than the end date. So in essence, your query should be 28-Feb-05 30-Nov-05 0 9 2 1-Mar-05 30-Nov-05 0 8 29 And it does give me proper results. The other way round would give some negative results in month. Mangesh "JE McGimpsey" wrote in message ... Hate to be continually sniping, but A B C 1 11/30/2005 02/28/2005 0 3 1 2 11/30/2005 03/01/2005 0 3 1 In article , "Mangesh" wrote: Hi JE, Ron, Ok. another minor adjustment: =(YEAR(B1)-YEAR(A1))-IF(AND(YEAR(A1)<YEAR(B1),OR(MONTH(A1)MONTH(B1),AN D(MON TH(A1)=MONTH(B1),DAY(A1)DAY(B1)))),1,0) & " " & (MONTH(B1)-MONTH(A1))-IF(AND(MONTH(A1)<MONTH(B1),DAY(A1)DAY(B1)),1,0)+I F(AN D(YEAR(A1)<YEAR(B1),MONTH(A1)MONTH(B1),DAY(A1)<=D AY(B1)),12,0)+IF(AND(YEAR( A1)<YEAR(B1),MONTH(A1)=MONTH(B1),DAY(A1)DAY(B1)) ,11,0)+IF(AND(MONTH(B1)<M ONTH(B1+1),DAY(A1)DAY(B1)),1,0) & " " & IF(DAY(B1)=DAY(A1),DAY(B1)-DAY(A1),IF(DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY (A1)<0,0,DAY(DATE(YEAR(B1),MONTH(B1),1)-1)-DAY(A1))+DAY(B1))-IF(AND(MONTH(B1 )<MONTH(B1+1),DAY(A1)DAY(B1)),DAY(B1)+DAY(DATE(Y EAR(A1),MONTH(A1)+1,1)-1 )- DAY(A1),0) |
#28
|
|||
|
|||
It was an error in my transcription. Try 11/30/2004. You get the same
result for both 2/28/2005 and 3/1/2005 In article , "Mangesh" wrote: no problem. But I forgot to mention that the start date has to be less than the end date. So in essence, your query should be 28-Feb-05 30-Nov-05 0 9 2 1-Mar-05 30-Nov-05 0 8 29 And it does give me proper results. The other way round would give some negative results in month. Mangesh "JE McGimpsey" wrote in message ... Hate to be continually sniping, but A B C 1 11/30/2005 02/28/2005 0 3 1 2 11/30/2005 03/01/2005 0 3 1 |
#29
|
|||
|
|||
On Tue, 24 May 2005 11:14:03 +0530, "Mangesh"
wrote: Ron, my whole idea at the moment is to avoid using any VBA and the analysis toolpak. And thats why I am trying to work my way through simple if conditions. Sometimes, VBA is easier. It is certainly simpler to debug <g. Avoiding the ATP is easy. I would just have to write an EOMONTH function in VBA. With your latest iteration, I think the following VBA routine mimics it, and seems simpler: ========================== Function DateIntvl(d1 As Date, d2 As Date) As String Dim temp As Date Dim i As Double Dim yr As Long, mnth As Long, dy As Long Do Until temp d2 i = i + 1 temp = DateAdd("m", i, d1) Loop i = i - 1 temp = DateAdd("m", i, d1) yr = Int(i / 12) mnth = i Mod 12 dy = d2 - temp DateIntvl = yr & " yrs " & mnth & " months " & dy & " days" End Function ======================= However, both it and your latest formula give the following results, which seem less than useful: 1/28/2005 2/28/2005 0 1 0 1/29/2005 2/28/2005 0 1 0 1/30/2005 2/28/2005 0 1 0 1/31/2005 2/28/2005 0 1 0 --------------------------------- My Calendar Month routine, (rewritten below so as to avoid the ATP reference), gives the following results for those same date intervals: 1/28/2005 2/28/2005 0 yrs 1 months 3 days 1/29/2005 2/28/2005 0 yrs 1 months 2 days 1/30/2005 2/28/2005 0 yrs 1 months 1 days 1/31/2005 2/28/2005 0 yrs 1 months 0 days ==================================== Function CalendarMonthsAndDays(d1 As Date, d2 As Date) As String Dim temp As Date Dim i As Double Dim yr As Long, mnth As Long, dy As Long Do Until temp = d2 i = i + 1 temp = EOM(d1, i) Loop If temp < d2 Then i = i - 1 End If yr = Int(i / 12) mnth = i Mod 12 dy = d2 - EOM(d1, i) + (EOM(d1, 0) - d1) CalendarMonthsAndDays = yr & " yrs " & mnth & " months " & dy & " days" End Function '--------------------- Function EOM(DT As Date, mnths As Double) As Date Dim Day1ofDT As Date Dim temp As Date Day1ofDT = DT - Day(DT) + 1 'add requisite number of months temp = DateAdd("m", mnths, Day1ofDT) 'go to end of month EOM = temp + 32 - Day(temp + 32) End Function =============================== --ron |
#30
|
|||
|
|||
Hi Ron,
I agree with you that sometimes VBA is an easier option. It was just that the query had come in the worksheet.functions group and I set myself upon the task of writing the formula. But it seems to be requiring some effort. If I get some time, will try to sort it out. By the way, thanks for all those tests by you and JE. Regards Mangesh "Ron Rosenfeld" wrote in message ... On Tue, 24 May 2005 11:14:03 +0530, "Mangesh" wrote: Ron, my whole idea at the moment is to avoid using any VBA and the analysis toolpak. And thats why I am trying to work my way through simple if conditions. Sometimes, VBA is easier. It is certainly simpler to debug <g. Avoiding the ATP is easy. I would just have to write an EOMONTH function in VBA. With your latest iteration, I think the following VBA routine mimics it, and seems simpler: ========================== Function DateIntvl(d1 As Date, d2 As Date) As String Dim temp As Date Dim i As Double Dim yr As Long, mnth As Long, dy As Long Do Until temp d2 i = i + 1 temp = DateAdd("m", i, d1) Loop i = i - 1 temp = DateAdd("m", i, d1) yr = Int(i / 12) mnth = i Mod 12 dy = d2 - temp DateIntvl = yr & " yrs " & mnth & " months " & dy & " days" End Function ======================= However, both it and your latest formula give the following results, which seem less than useful: 1/28/2005 2/28/2005 0 1 0 1/29/2005 2/28/2005 0 1 0 1/30/2005 2/28/2005 0 1 0 1/31/2005 2/28/2005 0 1 0 --------------------------------- My Calendar Month routine, (rewritten below so as to avoid the ATP reference), gives the following results for those same date intervals: 1/28/2005 2/28/2005 0 yrs 1 months 3 days 1/29/2005 2/28/2005 0 yrs 1 months 2 days 1/30/2005 2/28/2005 0 yrs 1 months 1 days 1/31/2005 2/28/2005 0 yrs 1 months 0 days ==================================== Function CalendarMonthsAndDays(d1 As Date, d2 As Date) As String Dim temp As Date Dim i As Double Dim yr As Long, mnth As Long, dy As Long Do Until temp = d2 i = i + 1 temp = EOM(d1, i) Loop If temp < d2 Then i = i - 1 End If yr = Int(i / 12) mnth = i Mod 12 dy = d2 - EOM(d1, i) + (EOM(d1, 0) - d1) CalendarMonthsAndDays = yr & " yrs " & mnth & " months " & dy & " days" End Function '--------------------- Function EOM(DT As Date, mnths As Double) As Date Dim Day1ofDT As Date Dim temp As Date Day1ofDT = DT - Day(DT) + 1 'add requisite number of months temp = DateAdd("m", mnths, Day1ofDT) 'go to end of month EOM = temp + 32 - Day(temp + 32) End Function =============================== --ron |
#31
|
|||
|
|||
Hi JE,
Thanks to you also for all the efforts. Will try to work on it once I get some time. Mangesh "JE McGimpsey" wrote in message ... It was an error in my transcription. Try 11/30/2004. You get the same result for both 2/28/2005 and 3/1/2005 In article , "Mangesh" wrote: no problem. But I forgot to mention that the start date has to be less than the end date. So in essence, your query should be 28-Feb-05 30-Nov-05 0 9 2 1-Mar-05 30-Nov-05 0 8 29 And it does give me proper results. The other way round would give some negative results in month. Mangesh "JE McGimpsey" wrote in message ... Hate to be continually sniping, but A B C 1 11/30/2005 02/28/2005 0 3 1 2 11/30/2005 03/01/2005 0 3 1 |
#32
|
|||
|
|||
Hi If let say I have start date: 15/12/04 and end date : 28/3/05 for a loan rollover period from last year. I need a formula to auto calculate the number of days for Jan 05, Feb 05 and Mar 05 separately in different rows. how should I go about it? "PC" wrote: Possibly this will work =DATEDIF(A1,B1,"Y")&" Years "&DATEDIF(A1,B1,"YM")&" Months "&DATEDIF(A1,B1,"Md")&" Days" Startdate is in A1, Enddate is in B1 HTH PC "Bluenose" wrote in message ... Hello. I have a function that allows me to calculate the length of time between two dates. =YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1), DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1) <=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<M ONTH(A1),DAY(B1) =DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)<D AY(A1)),-1)))&" months, "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days" I have a list of dates in adjacent columns. I want to be able to display the result of this function in a third column, for the two dates in each row. Can anyone tell me whether or not it is possible to somehow do this more easily than pasting the function into each cell of the 3rd column and changing the cell references in the funtion manually? This is not practical as the columns are too large! I only have a limited knowledge of Excel so I apologise if this is a simple query. Hope you clever people can help! Many thanks |
#33
|
|||
|
|||
On Wed, 29 Jun 2005 02:51:04 -0700, "Jesline"
wrote: Hi If let say I have start date: 15/12/04 and end date : 28/3/05 for a loan rollover period from last year. I need a formula to auto calculate the number of days for Jan 05, Feb 05 and Mar 05 separately in different rows. how should I go about it? A formula for days in a month, with any date in that month in A1, is: =32-DAY(A1-DAY(A1)+32) --ron |
#34
|
|||
|
|||
Hi
A1="StartDate" A2="EndDate" A3="Days1" A4="Days2" etc. Into B1 and B2 enter start and end dates respectively. B3=IF(DATE(YEAR($B$1),MONTH($B$1)+ROW()-3,1)<$B$2,(DAY(MIN($B$2,DATE(YEAR($B$1),MONTH($B$1 )+ROW()-2,0)))-DAY(MAX($B$1,DATE(YEAR($B$1),MONTH($B$1)+ROW()-3,1)))+1),"") Copy the formula in B3 down for as much rows you think you will need months maximally. (Start and end dates are included into count of days, for abundant months nothing - an empty string - is returned.) -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Jesline" wrote in message ... Hi If let say I have start date: 15/12/04 and end date : 28/3/05 for a loan rollover period from last year. I need a formula to auto calculate the number of days for Jan 05, Feb 05 and Mar 05 separately in different rows. how should I go about it? "PC" wrote: Possibly this will work =DATEDIF(A1,B1,"Y")&" Years "&DATEDIF(A1,B1,"YM")&" Months "&DATEDIF(A1,B1,"Md")&" Days" Startdate is in A1, Enddate is in B1 HTH PC "Bluenose" wrote in message ... Hello. I have a function that allows me to calculate the length of time between two dates. =YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1), DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1) <=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<M ONTH(A1),DAY(B1) =DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)<D AY(A1)),-1)))&" months, "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days" I have a list of dates in adjacent columns. I want to be able to display the result of this function in a third column, for the two dates in each row. Can anyone tell me whether or not it is possible to somehow do this more easily than pasting the function into each cell of the 3rd column and changing the cell references in the funtion manually? This is not practical as the columns are too large! I only have a limited knowledge of Excel so I apologise if this is a simple query. Hope you clever people can help! Many thanks |
#35
|
|||
|
|||
Hey all... I thought I might tackle the year - month - day problem, but I have a few questions first. From the previous posts I concluded that a full month equals both of the following: An end date that is the same day of the month as the start date ex. 15 Jan 2005 & 15 Feb 2005 should be 0-1-0 An end date that is the end of the month ex. 28 Feb 2005 counts as a full month presuming that the start date is prior to Feb. This brings up a problematic question.... If the start month has more days than the end month AND the end date is the last day of the month while the start date is not eom but still = the end date..... Ex: Start: 28 Jan 2005 END: 28 Feb 2005 Should the result be 0-1-0 or 0-1-3? ( the 28th to the 28th is 1 month so what about the 3 days left in Jan?) It get even weirder as you progress the dates: 28 Jan 2005 to 27 Feb 2005 = 0-0-30 (3 days in Jan + 27 in Feb) 28 Jan 2005 to 28 Feb 2005 = 0-1-0 ? or 0-1-3 (3 days left in Jan + Feb, yet the 28th to the 28th should be a month) but how can you go from 0-0-30 to 0-1-3 by changing just one day? 28 Jan 2005 to 01 Mar 2005 = 0-1-1 ? or 0-1-4 (3 days left in Jan + Feb + 1 day March yet 1-28 to 2-28=1 month +1 day for March)) 29 Jan 2005 to 01 Mar 2005 = 0-1-3 (2 days left in Jan + Feb + 1 day March) 30 Jan 2005 to 01 Mar 2005 = 0-1-2 (1 day left in Jan + Feb + 1 day March) Anyway, notwithstanding clarification on this problem, here is my solution: =IF(MONTH(B1)<MONTH(A1),YEAR(B1)-YEAR(A1)-1,YEAR(B1)-YEAR(A1))&" Years "& IF(MONTH(A1)MONTH(B1),IF(DAY(B1)=DAY(EOMONTH(B1,0 )),MONTH(B1)-MONTH(A1)+12,MONTH(B1)-MONTH(A1)+11),IF(DAY(B1)=DAY(EOMONTH(B1,0)),MONTH( B1)-MONTH(A1),IF(DAY(B1)DAY(A1),MONTH(B1)-MONTH(A1),MONTH(B1)-MONTH(A1)-1)))&" Months "& IF(DAY(B1)=DAY(EOMONTH(B1,0)),DAY(EOMONTH(A1,0))-DAY(A1),IF(DAY(B1)<DAY(A1),DAY(EOMONTH(A1,0))-DAY(A1)+DAY(B1),DAY(B1)-DAY(A1)))&" Days" GrizzlyBear -- Grizzly Bear ------------------------------------------------------------------------ Grizzly Bear's Profile: http://www.excelforum.com/member.php...fo&userid=1838 View this thread: http://www.excelforum.com/showthread...hreadid=372455 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
working with dates in fiscal years | Excel Worksheet Functions | |||
how to calculate years, months by using one formula | Charts and Charting in Excel | |||
Calculating interest on number of days in the period | Excel Worksheet Functions | |||
Calculate months and years | Excel Discussion (Misc queries) | |||
How do I display months and years between two dates | Excel Discussion (Misc queries) |