Confusing results
in [Functions]
|
Prev: VLook-Up Possibly Conditional Formatting
Next: count total cells if they are a certain color-reverse conditional
From: Papa Jonah on 5 Mar 2010 13:34 In cells H15 and H36 I have 10/31/09 and 1/31/10 respectively. In J 15 and J36 I have the following formula: =DATE(YEAR(H15),MONTH(H15-DAY(H15))+2,) Cell J36 has With H36 instead of H15. The intent is to identify the last date of the month identified in column H. However, the results are: H J 10/31/09 10/31/09 (This worked) 1/31/10 1/31/11 (This added a whole year) The equation seems to work every where unless the date in column H is in January. Column L does something similar to calculate the end of the subsequent month. It works in all cases. The formula I used for that is: =DATE(YEAR(H36),MONTH(H36-DAY(H36)+2)+2,) Why isn't the first formula working in every case? TIA Papa J
From: Eduardo on 5 Mar 2010 13:41 Hi, If your data is exactly the same as posted your formula added a year because in column H you have 1/31/10 and in column J 1/31/11 just a year so formula works "Papa Jonah" wrote: > In cells H15 and H36 I have 10/31/09 and 1/31/10 respectively. > In J 15 and J36 I have the following formula: > > =DATE(YEAR(H15),MONTH(H15-DAY(H15))+2,) > Cell J36 has With H36 instead of H15. > The intent is to identify the last date of the month identified in column H. > > However, the results are: > H J > 10/31/09 10/31/09 (This worked) > 1/31/10 1/31/11 (This added a whole year) > > The equation seems to work every where unless the date in column H is in > January. > > Column L does something similar to calculate the end of the subsequent > month. It works in all cases. The formula I used for that is: > =DATE(YEAR(H36),MONTH(H36-DAY(H36)+2)+2,) > > Why isn't the first formula working in every case? > > TIA > > Papa J
From: Papa Jonah on 5 Mar 2010 15:36 But as I indicated, the intent is to identify the last day of the month - the intent is not to add a year. The rest of the cells did not add a year making all the other cells with the desired results as opposed to this one example from January. "Eduardo" wrote: > Hi, > If your data is exactly the same as posted your formula added a year because > in column H you have 1/31/10 and in column J 1/31/11 just a year so formula > works > > "Papa Jonah" wrote: > > > In cells H15 and H36 I have 10/31/09 and 1/31/10 respectively. > > In J 15 and J36 I have the following formula: > > > > =DATE(YEAR(H15),MONTH(H15-DAY(H15))+2,) > > Cell J36 has With H36 instead of H15. > > The intent is to identify the last date of the month identified in column H. > > > > However, the results are: > > H J > > 10/31/09 10/31/09 (This worked) > > 1/31/10 1/31/11 (This added a whole year) > > > > The equation seems to work every where unless the date in column H is in > > January. > > > > Column L does something similar to calculate the end of the subsequent > > month. It works in all cases. The formula I used for that is: > > =DATE(YEAR(H36),MONTH(H36-DAY(H36)+2)+2,) > > > > Why isn't the first formula working in every case? > > > > TIA > > > > Papa J
From: "David Biddulph" groups [at] on 5 Mar 2010 15:40 If H15 is 31/1/10, MONTH will return 12, you've then added 2 to make it 14, hence 31/1/11 sounds like the answer you would expect from that formula. I'm not sure why you are using =DATE(YEAR(H15),MONTH(H15-DAY(H15))+2,) Why not =DATE(YEAR(H15),MONTH(H15)+1,0) ? -- David Biddulph "Papa Jonah" <PapaJonah(a)discussions.microsoft.com> wrote in message news:3E34CEAB-EB47-4BD1-8291-E866C6FA98B9(a)microsoft.com... > In cells H15 and H36 I have 10/31/09 and 1/31/10 respectively. > In J 15 and J36 I have the following formula: > > =DATE(YEAR(H15),MONTH(H15-DAY(H15))+2,) > Cell J36 has With H36 instead of H15. > The intent is to identify the last date of the month identified in column > H. > > However, the results are: > H J > 10/31/09 10/31/09 (This worked) > 1/31/10 1/31/11 (This added a whole year) > > The equation seems to work every where unless the date in column H is in > January. > > Column L does something similar to calculate the end of the subsequent > month. It works in all cases. The formula I used for that is: > =DATE(YEAR(H36),MONTH(H36-DAY(H36)+2)+2,) > > Why isn't the first formula working in every case? > > TIA > > Papa J
From: Ron Rosenfeld on 5 Mar 2010 16:07
On Fri, 5 Mar 2010 10:34:01 -0800, Papa Jonah <PapaJonah(a)discussions.microsoft.com> wrote: >In cells H15 and H36 I have 10/31/09 and 1/31/10 respectively. >In J 15 and J36 I have the following formula: > >=DATE(YEAR(H15),MONTH(H15-DAY(H15))+2,) > Cell J36 has With H36 instead of H15. >The intent is to identify the last date of the month identified in column H. > >However, the results are: > H J >10/31/09 10/31/09 (This worked) >1/31/10 1/31/11 (This added a whole year) > >The equation seems to work every where unless the date in column H is in >January. > >Column L does something similar to calculate the end of the subsequent >month. It works in all cases. The formula I used for that is: >=DATE(YEAR(H36),MONTH(H36-DAY(H36)+2)+2,) > >Why isn't the first formula working in every case? > >TIA > >Papa J To return the last day of the month, with a date in H15 =date(year(h15),month(h15)+1,0) or, if you have Excel 2007+ or an earlier version with the Analysis Tool Pak installed: =eomonth(h15,0) --ron |