How to show decimal value in Monthly function ?

  • Thread starter Thread starter johnnymo
  • Start date Start date
J

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
 
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?
 
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.
 
Back
Top