How to show decimal value in Monthly function ?

[johnnymo]

When showing the difference value in the monthly function
ie June 1 plus July 15th = , how do I get the result to
display 1.5 instead of 1 (it seems to be doing 6+1 ) ?

Any help here is appreciated, thanks so much.

JM

 

[Frank Kabel]

Hi
not very accurate but try
=(B1-A1)/30
if B1 and A1 stores your dates. But as said not very
accurate. what would be your expected result for
1-Dec-03
and
15-Feb-04

2.5?

 

[Norman Harker]

Hi JM!

One approach:

=DATEDIF(A1,B1,"m")+DAY(B1)/DAY(DATE(YEAR(B1),MONTH(B1)+1,0))

Start date is in A1
End Date is in B1

The DATEDIF bit calculates the completed months. The rest of the
formula calculates the fraction of the end date month represented by
the day.

With
A1: 1-Jun-2004
B1: 15-Jul-2004
Returns: 1.48387096774194
Format General
Or if you format as a fraction 1 15/31

You could take the average number of days in a month as the
denominator but somehow I prefer to take the days in the "terminal"
month. We used that principle to calculate fractions of a year when we
discovered that YEARFRAC was producing errors.