Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
count months
can anyone pls suggest that what is best way to calculate months between two
dates. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
count months
Hi,
It depends what you mean by 'between' but as a start try this =datedif(A1,B1,"m") For help on the undocumented datedif look here http://www.cpearson.com/excel/datedif.htm Mike "Satyendra_Haldaur" wrote: can anyone pls suggest that what is best way to calculate months between two dates. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
count months
With the earlier date in A1:
=DATEDIF(A1,B1,"m") Check out this web page of Chip Pearson for an explanation of this undocumented (except for XL2k) function. http://www.cpearson.com/excel/datedif.aspx -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Satyendra_Haldaur" wrote in message ... can anyone pls suggest that what is best way to calculate months between two dates. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
count months
DATEDIF(StartDate,EndDate,"m") **may** be what you are looking for; but then
again, it may not. You need to understand how it "counts" months in order to decide. Consider a start date of March 15, 2009 and an end date of June 15, 2009... DATEDIF will report this as 3 months; **however**, change the start date to March 16, 2009 and DATEDIF now reports this as 2 months. It appears that DATEDIF counts full months starting its count from the starting date. Is that what you wanted? -- Rick (MVP - Excel) "Satyendra_Haldaur" wrote in message ... can anyone pls suggest that what is best way to calculate months between two dates. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
count months
If you wanted the result to always be 3 (inclusive of all dates) you can try:
=DATEDIF(DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(B1), MONTH(B1),1),"m") That changes both dates to the first of both months used in the DATEDIF() so the 15th/16th problem doesn't come into play. I actually tried using the EOMONTH() function, but that was still susceptible to the variance depending on the end date of the months involved. "Rick Rothstein" wrote: DATEDIF(StartDate,EndDate,"m") **may** be what you are looking for; but then again, it may not. You need to understand how it "counts" months in order to decide. Consider a start date of March 15, 2009 and an end date of June 15, 2009... DATEDIF will report this as 3 months; **however**, change the start date to March 16, 2009 and DATEDIF now reports this as 2 months. It appears that DATEDIF counts full months starting its count from the starting date. Is that what you wanted? -- Rick (MVP - Excel) "Satyendra_Haldaur" wrote in message ... can anyone pls suggest that what is best way to calculate months between two dates. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
count months
Or, for inclusive months, maybe this...
=Month(B1)-Month(A1) -- Rick (MVP - Excel) "JLatham" wrote in message ... If you wanted the result to always be 3 (inclusive of all dates) you can try: =DATEDIF(DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(B1), MONTH(B1),1),"m") That changes both dates to the first of both months used in the DATEDIF() so the 15th/16th problem doesn't come into play. I actually tried using the EOMONTH() function, but that was still susceptible to the variance depending on the end date of the months involved. "Rick Rothstein" wrote: DATEDIF(StartDate,EndDate,"m") **may** be what you are looking for; but then again, it may not. You need to understand how it "counts" months in order to decide. Consider a start date of March 15, 2009 and an end date of June 15, 2009... DATEDIF will report this as 3 months; **however**, change the start date to March 16, 2009 and DATEDIF now reports this as 2 months. It appears that DATEDIF counts full months starting its count from the starting date. Is that what you wanted? -- Rick (MVP - Excel) "Satyendra_Haldaur" wrote in message ... can anyone pls suggest that what is best way to calculate months between two dates. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
count months
Only if they are both in the same year.
-- David Biddulph Rick Rothstein wrote: Or, for inclusive months, maybe this... =Month(B1)-Month(A1) "JLatham" wrote in message ... If you wanted the result to always be 3 (inclusive of all dates) you can try: =DATEDIF(DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(B1), MONTH(B1),1),"m") That changes both dates to the first of both months used in the DATEDIF() so the 15th/16th problem doesn't come into play. I actually tried using the EOMONTH() function, but that was still susceptible to the variance depending on the end date of the months involved. "Rick Rothstein" wrote: DATEDIF(StartDate,EndDate,"m") **may** be what you are looking for; but then again, it may not. You need to understand how it "counts" months in order to decide. Consider a start date of March 15, 2009 and an end date of June 15, 2009... DATEDIF will report this as 3 months; **however**, change the start date to March 16, 2009 and DATEDIF now reports this as 2 months. It appears that DATEDIF counts full months starting its count from the starting date. Is that what you wanted? -- Rick (MVP - Excel) "Satyendra_Haldaur" wrote in message ... can anyone pls suggest that what is best way to calculate months between two dates. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
count months
99 programmers = 198 solutions!! :-)
"Rick Rothstein" wrote: Or, for inclusive months, maybe this... =Month(B1)-Month(A1) -- Rick (MVP - Excel) "JLatham" wrote in message ... If you wanted the result to always be 3 (inclusive of all dates) you can try: =DATEDIF(DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(B1), MONTH(B1),1),"m") That changes both dates to the first of both months used in the DATEDIF() so the 15th/16th problem doesn't come into play. I actually tried using the EOMONTH() function, but that was still susceptible to the variance depending on the end date of the months involved. "Rick Rothstein" wrote: DATEDIF(StartDate,EndDate,"m") **may** be what you are looking for; but then again, it may not. You need to understand how it "counts" months in order to decide. Consider a start date of March 15, 2009 and an end date of June 15, 2009... DATEDIF will report this as 3 months; **however**, change the start date to March 16, 2009 and DATEDIF now reports this as 2 months. It appears that DATEDIF counts full months starting its count from the starting date. Is that what you wanted? -- Rick (MVP - Excel) "Satyendra_Haldaur" wrote in message ... can anyone pls suggest that what is best way to calculate months between two dates. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
count months
Or, if the dates can be in different years,
=year(b1)*12+month(b1)-year(a1)*12-month(a1) Regards, Fred. "Rick Rothstein" wrote in message ... Or, for inclusive months, maybe this... =Month(B1)-Month(A1) -- Rick (MVP - Excel) "JLatham" wrote in message ... If you wanted the result to always be 3 (inclusive of all dates) you can try: =DATEDIF(DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(B1), MONTH(B1),1),"m") That changes both dates to the first of both months used in the DATEDIF() so the 15th/16th problem doesn't come into play. I actually tried using the EOMONTH() function, but that was still susceptible to the variance depending on the end date of the months involved. "Rick Rothstein" wrote: DATEDIF(StartDate,EndDate,"m") **may** be what you are looking for; but then again, it may not. You need to understand how it "counts" months in order to decide. Consider a start date of March 15, 2009 and an end date of June 15, 2009... DATEDIF will report this as 3 months; **however**, change the start date to March 16, 2009 and DATEDIF now reports this as 2 months. It appears that DATEDIF counts full months starting its count from the starting date. Is that what you wanted? -- Rick (MVP - Excel) "Satyendra_Haldaur" wrote in message ... can anyone pls suggest that what is best way to calculate months between two dates. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count the number of months between two dates | Excel Worksheet Functions | |||
Count occurances of a name over previous 12 months | Excel Worksheet Functions | |||
count months between years | Excel Worksheet Functions | |||
COUNT MONTHS | Excel Worksheet Functions | |||
Count certain months | Excel Worksheet Functions |