Excel Functions&Formulas >> Help Calculating Partial Months between 2 dates.

by sirscottyog » Thu, 16 Apr 2009 01:25:34 GMT

Hi there. I am trying to obtain the number of months (to 2 decimal
places) between two dates:
w4 is 4/4/09
x4 is 6/30/09

Any ideas? Searched extensively but have not come up with a
solution. Client needs to show exact number of months to 2 decimal
places...

Thanks!!

Excel Functions&Formulas >> Help Calculating Partial Months between 2 dates.

by T. Valko » Thu, 16 Apr 2009 01:35:42 GMT


>show exact number of months to 2 decimal places...

What result do you expect based on your sample?

The problem with this is that a month is not a standard unit of measure. A
month can have 28,29,30 or 31 days.


--
Biff
Microsoft Excel MVP

Excel Functions&Formulas >> Help Calculating Partial Months between 2 dates.

by Ashish Mathur » Thu, 16 Apr 2009 10:29:35 GMT

Hi,

To get the completed months, use =DATEDIF(C4,C5,"m") where C4 is the
beginning date and C5 is the ending date. To get the balance months, use
=DATEDIF(C4,C5,"md")

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

Similar Threads

1. Calculate the number of months between dates include partial month - Excel Functions&Formulas

2. Calculate the number of months between dates include partial m

THANKS A LOT

"JE McGimpsey" wrote:

> How are you figuring "the difference between two dates in months" in 
> your example as 2, when it's only 17 days?
> 
> If you're counting any date in January as a partial month and any date 
> in February as a partial month, and totalling that to 2 partial months, 
> here's one way:
> 
> A1:     1/15/2006
> A2:     2/1/2006
> A3:     =(YEAR(A2)-YEAR(A1))*12+(MONTH(A2)-MONTH(A1))+1
> 
> 
> 
> 
> In article < XXXX@XXXXX.COM >,
>  JP < XXXX@XXXXX.COM > wrote:
> 
> > I understand datedif, but is there a way to calculated the difference between 
> > two dates in months and include any partial months.
> > 
> > i.e. - 1/15/2006 and 2/1/2006 = 2 months
> 

3. Calculating Dates by Month - please help!!!

4. Dates to months and calculating values for their months

I want to put dates to their months for coloumn A and work out the
monthly totals of column B according to month. Not using a pivot
table.

A >>>>>>>> B
Date >>>>>>Litres 
01-Jan-03 >>4185 
05-Jan-03 >>4132 
14-Jan-03 >>4132 
01-Feb-03 >>4132 
01-Mar-03 >>4616 
01-Apr-03 >>5223

i would like the output to be as follows:

A >>>>>B
jan-03 >>feb-03 subtract jan-03/number of days in jan * 1000
feb-03 >>mar-03 subract feb-03/number of days in feb * 1000


The problem i have is repeating months in cells fill down
A >>> B
jan-03 xxxx
jan-03 xxxx
feb-03 xxxx

Please help or suggest any ideas, i could do this in a pivot table but
i need to automate macros for this.

Thanks
Lai

5. Calculating recurring date in following month, calculating # days in that period - Excel Functions&Formulas

6. Calculating recurring date in following month, calculating # days

Instead of EOMONTH(), try EDATE()

=EDATE(earlier date,1)

will give you the same DAY next month.  However, if you use it on a Jan 31 
start date, it'll give you the lat day of February.

Then just subtract the two dates to get the # of days between them


"Walterius" wrote:

> Excel 2002. I have a simple spreadsheet that logs my expenses by month.
> 
> I haven't been able to teach Excel how to calculate the same day next month.
> E.g., the 3rd of June is 06/03/05, so the 3rd of the next month should be
> 07/03/05.
> 
> I also need the number of days in that period, e.g. from 6/03/05 to 7/03/05
> = 30 days (because there are 30 days in June). I don't know how to calculate
> that either.
> 
> Attempts to use the EOMONTH function fail, and I can't find anything else
> that looks suitable. I have been using Excel for years and I am still an
> Excel klutz. :-(
> 
> Many thanks for your help.
> 
> Walterius
> 
> 
> 

7. Calculate 1st of month date from existing date. - Excel

8. About calculate months between two date (date function)

Hi, I find out there is no direct function to calculate total months between 
two dates, such as I want to calculate how many month from an start date to 
year end date (like 12/31/2006), I do figure out the date function but it 
looks very complicated, is any easy way?? mine is 

=(YEAR(12/31/2006)-YEAR(1/1/2003))*12+(12-MONTH(1/1/2003))+1

the total month is 48, is any easy way???

THANKS
Hank