Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Confusing results
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 a 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Confusing results
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 a 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Confusing results
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 a 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Confusing results
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" wrote in message ... 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 a 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Confusing results
On Fri, 5 Mar 2010 10:34:01 -0800, 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 a 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Confusing results
Thanks David. I don't understand your explanation why I should get what I
got - especially since the other cells did not add a year. But your suggestion worked beautifully. The reason I didn't do that before is I didn't figure it out! Thanks "David Biddulph" wrote: 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" wrote in message ... 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 a 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Menu confusing | New Users to Excel | |||
Confusing Function | Excel Worksheet Functions | |||
V-/H- LOOKUP still confusing | Excel Worksheet Functions | |||
VLookup confusing | Excel Worksheet Functions | |||
Confusing Problem | Excel Discussion (Misc queries) |