Calculate a future date
Am a new user, have developed a database for vehicle service. I want to calculate months (6, 12, 18, 36) from the completed date that the next inspection is due.
Am using: if 'Service Type' = 4 and 'Miles or Months' = 2 and 'Item' = 5 then (completed date) + (months)
else null;
end
This works, but returned 1560582000000
12 replies
-
https://ninoxdb.de/en/manual/calculations/reference-of-functions-and-language
Try using the date() function. The result was 06/15/2019 for me.
-
Entered this strig:
if 'Service Type' = 4 and 'Miles or Months' = 2 and Item = 8 then
today() + 365
elseit works, but returns a very large number. How can I change it to show a future date? The string should display 1 year from now?
-
Hi,
please try
... then date(today() + 365)else....Best, Jörg
-
Thank you. I tried that but it returns a huge number in the field. At first I thought it was the number of seconds in a year, but it is much larger than 31,557,600.
Surely im doing something wrong!
-
Hi,
Where did you enter the script? In a formula field?
Is that the complete script?
Kind regards, Jörg
-
Yes sir, it is a formula field.
I have a database to track vehicle maintenance. Part of the information is inspection or replacement. Some requirements are based on Months, others on Miles.This is the string to determine next Due date:
if 'Service Type' = 4 and 'Miles or Months' = 1 and Item = 1 then
'Current Mileage' + 3500
else
if 'Service Type' = 4 and 'Miles or Months' = 1 and Item = 2 then
'Current Mileage' + 3500
else
if 'Service Type' = 4 and 'Miles or Months' = 1 and Item = 3 then
'Current Mileage' + 5000
else
if 'Service Type' = 4 and 'Miles or Months' = 1 and Item = 4 then
'Current Mileage' + 75000
else
if 'Service Type' = 4 and 'Miles or Months' = 2 and Item = 5 then
date(today() + 365)
else
if 'Service Type' = 4 and 'Miles or Months' = 2 and Item = 8 then
date(today() + 365)
else
if 'Service Type' = 4 and 'Miles or Months' = 2 and Item = 7 then
date(today() + 1080)
else
if 'Service Type' = 4 and 'Miles or Months' = 2 and Item = 6 then
date(today() + 1080)
else
null
end
end
end
end
end
end
end
end
The code works fine for miles, but for months, it returns a huge number.
Sent from my iPad
-
Make sure Number format is empty...
-
Yes sir, it is empty.
-
I see your formula is different - is that for your test?
-
I just picked an existing formula of mine that returned a date so I could eliminate the easy solutions first. I just now got a chance to look at your formula more closely and it looks like the issue is you are combining 2 different return types in the if-then-else statements... Number and Date.
Create a new Formula field and use the date() function and your original Formula field name as the argument it will show the date when, for example, the condition...
if 'Service Type' = 4 and 'Miles or Months' = 2 and Item = 7 thendate(today() + 1080)is met. Or, if you were to separate the Number if-then-else statements from the Date if-then-else statements they would both work correctly.
-
I broke the miles string apart from the months and tested each individually. Each worked as desired. My next step is to end the string at eye end of miles with “end” then insert the Months string. Just haven’t had a chance yet. Thank you for your assistance
-
Hi,
We would like to understand your goal better. It would be great if you could bring up your question in our free webinar on Tuesdays.
At our Ninox webinar tips and tricks will be presented in the first 30 minutes, the remaining 90 minutes will be used for a live support where the participants' concerns will be solved.
You can register here for the webinar, which takes place every Tuesday at 18 o'clock CEST: https://zoom.us/webinar/register/WN_zoSk2qyNSz6vLRFF1zlakg
Best regards, Jörg
Content aside
- 5 yrs agoLast active
- 12Replies
- 3335Views