Folmula Help

[Kathy]

I'm trying to calculate the remaining lease term of an auto using the
following formula =[lease end date] - today()/365*12 , but it returns a #Name
error?

Example: We have an auto with a lease end date of 03/05/2008 our calculation
should return -12.3945. Please help?

 

[Al Campagna]

Kathy,
Try...
=DateDiff("h",Now(),[lease end date])/24

Check out the DateDiff function in Help.
And, check out Now() and Date().
There is no Today function in Access.
Now() returns the date and time, Date() returns just the date.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[John W. Vinson]

I'm trying to calculate the remaining lease term of an auto using the
following formula =[lease end date] - today()/365*12 , but it returns a #Name
error?

Example: We have an auto with a lease end date of 03/05/2008 our calculation
should return -12.3945. Please help?

Access uses the function Date() to get the computer clock date, rather than
today() - probably the source of your error. However, I'd suggest using the
builtin DateDiff function instead:

=DateDiff("d", Date(), [lease end date])

I'm not sure I understand your expression though - it seems to be subtracting

Today()/365*12

from the lease end date. That value will be a peculiar number dependent on the
starting point for the date function. In addition, your 365 neglects leap
years. Could you describe the meaning of the "remaining lease term" in words?

 

[Kathy]

For our purposes, lease term is 38 months. If the car's lease is up March
31, 2012, I want to know exactly how many months are left on the lease. For
today I am showing 36.5 months left on the lease in Excel. I would like to
have this calculation on the Access form.

I'm trying to calculate the remaining lease term of an auto using the
following formula =[lease end date] - today()/365*12 , but it returns a #Name
error?

Example: We have an auto with a lease end date of 03/05/2008 our calculation
should return -12.3945. Please help?

Access uses the function Date() to get the computer clock date, rather than
today() - probably the source of your error. However, I'd suggest using the
builtin DateDiff function instead:

=DateDiff("d", Date(), [lease end date])

I'm not sure I understand your expression though - it seems to be subtracting

Today()/365*12

from the lease end date. That value will be a peculiar number dependent on the
starting point for the date function. In addition, your 365 neglects leap
years. Could you describe the meaning of the "remaining lease term" in words?

 

[John W. Vinson]

For our purposes, lease term is 38 months. If the car's lease is up March
31, 2012, I want to know exactly how many months are left on the lease. For
today I am showing 36.5 months left on the lease in Excel. I would like to
have this calculation on the Access form.
"John W. Vinson" wrote:

Leaving aside the question of fractional months (0.5 Februarys is less than
0.5 Marches), try

DateDiff("d", [Lease End Date], Date()) * 12 / 365

for a decent approximation.