Salesforce and other SMB Solutions are coming soon. ×

# Date Calculation - Car Registration Expiry / Car Age

## Recommended Posts

I am having trouble, and I am not to great at scripting.

I aim to have a Field that calculates how much time is remaining before a cars Registration is due for renewel.

I have a Field for Registration Expiry (Rego Expiry) and then a Field/Calculation (Rego Remaining) that I want to display Rego Left.

Is it possible in that if their were 1 month left on the registration that the Font was change to Red to make it more obvious.

____

I also have another issue.

I want a Calculation field that also determines the age of the car, and how long we have had it for.

I have a field for Year Model (Year Model) and a field for Purchase Date (Purcahse Date) and I am trying to get 2 calculations that determine How old the car is, and how long we have had the car for.

Our company has plenty of cars, and we are buying many cars frequently. It is easy to look at papers and such, but with this information in front of us right away, it would life easier.

I really appreciate those who help me.

##### Share on other sites

I'll assume you don't have FileMaker 9 (the red colour could be done using conditional formatting, but I'll put it in the calculation

Let ([

CurrentDate = Get ( CurrentDate ) ;

Expiry = Rego Expiry ;

ExpiryRed = TextColor ( Expiry ; RGB ( 220 ; 0 ; 0 ) ) ;

DaysBeforeExpiry = Expiry - CurrentDate

];

if ( DaysBeforeExpiry

)

- It is a little grey around the month part, basically says 31 days and less until expiry is put in red , if you wanted to be more precise you could check the current month number, then change the 32 to whatever the number of days in current month is etc... but to keep things simple I put it @ 32

##### Share on other sites

How old car is:

Year ( Get ( CurrentDate ) ) - Year Model

Assuming year model field contains a year.

---

How long you have had car for:

Get ( CurrentDate ) - Purchase Date

- this will return the number of days you have had the car, from this you can do whatever you want to display it how you please.

To make life easier, here is the calculation returning years/months/days etc, credit goes to Winfried Huslik from http://www.fmdiff.com who wrote the original custom function (http://www.briandunning.com/cf/518)

Let ( [

date1 = Purchase Date ;

date2 = Get ( CurrentDate ) ;

neg = Case ( date1 > date2 ; -1 ; 1 ) ;

d1 = Case ( neg

d2 = Case ( neg

d = mod ( Day ( d2 ) - Day ( d1 ) ; Day (

Date ( Month ( d1 ) + 1 ; 0; year ( d1 ) ) ) ) ;

m = mod ( Month ( d2 ) - Month ( d1 ) -

( Day ( d2 )

y = Year ( d2 ) - Year ( d1 ) - ( ( Month ( d2 ) -

( Day ( d2 )

];

y * neg & Â¶ &

m * neg & Â¶ &

d * neg & Â¶ &

y & " years, " & m & " months, and " & d & " days" & Â¶

)

I havent tested the above calc, merely adapted it from the custom function, but I'm confident it should work.

##### Share on other sites

I'll assume you don't have FileMaker 9

We have a copy of FilemakerPro 9.

Thank you for responding and helping.

I will put these calculations into my database and let you know how I go.

Again appreciate the response

• ### Images

• By Soliant Consulting,
• By Soliant Consulting,
• By Soliant Consulting,
• ### Forum Statistics

• Total Topics
33.7k
• Total Posts
141.6k
×
×
• Create New...