DateDiff Expression

  • Thread starter Thread starter Mike S.
  • Start date Start date
M

Mike S.

I am creating a bound form that calculates a Date
Difference using the expression format provided by
Microsoft:
=DateDiff("m", Date(), [Compliance-Due])

The result of the expression works OK, but I need the
result to return in tenths of a month. This form control
is already formatted to include one decimal place as a
fixed number, so the returned value is a whole number plus
that one decimal place (ie 14.0).

Is there a way to revise the expression to calculate the
difference in months, including the tenths of a month?

Thanks in advance for your help.
Mike S.
 
The question is 'how do you calculate tenths of a month?"

Here is an example of 1 way it could be done assuming that
1 month = 365.25/12 days

=Round(DateDiff("d", Date(), [Compliance-Due]) * (12 /
365.25) , 1)

The above is aircode

Hope This Helps
Gerald Stanley MCSD
 
I am creating a bound form that calculates a Date
Difference using the expression format provided by
Microsoft:
=DateDiff("m", Date(), [Compliance-Due])

The result of the expression works OK, but I need the
result to return in tenths of a month. This form control
is already formatted to include one decimal place as a
fixed number, so the returned value is a whole number plus
that one decimal place (ie 14.0).

Is there a way to revise the expression to calculate the
difference in months, including the tenths of a month?

Thanks in advance for your help.
Mike S.
Click to expand...

Since the number of days is a month can vary from 28 to 31, showing a
tenth of a month is not going to be very accurate. If you can live
with that inaccuracy, lets divide the number of days in a year by 12
months (365.25 /12 = 30.4) to arrive at the average number of days in
a month.

=DateDiff("d",Date(),[Compliance-Due])/30.4
will return the following:
75 days = 2.46 rounded to 2.5 months
33 days = 1.08 rounded to 1.1 months
122 days = 4.01 rounded to 4.0
 
-----Original Message-----
I am creating a bound form that calculates a Date
Difference using the expression format provided by
Microsoft:
=DateDiff("m", Date(), [Compliance-Due])

The result of the expression works OK, but I need the
result to return in tenths of a month. This form control
is already formatted to include one decimal place as a
fixed number, so the returned value is a whole number plus
that one decimal place (ie 14.0).

Is there a way to revise the expression to calculate the
difference in months, including the tenths of a month?

Thanks in advance for your help.
Mike S.
Click to expand...

Since the number of days is a month can vary from 28 to 31, showing a
tenth of a month is not going to be very accurate. If you can live
with that inaccuracy, lets divide the number of days in a year by 12
months (365.25 /12 = 30.4) to arrive at the average number of days in
a month.

=DateDiff("d",Date(),[Compliance-Due])/30.4
will return the following:
75 days = 2.46 rounded to 2.5 months
33 days = 1.08 rounded to 1.1 months
122 days = 4.01 rounded to 4.0

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.
Click to expand...

Thanks, Fred. I went with "'d'" and "/30.4", and it worked
like a charm. Thats the result I was looking for. The
accuracy is close enough.
 
Back
Top