0

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

null
    • Sean
    • 4 yrs ago
    • Reported - view

    https://ninoxdb.de/en/manual/calculations/reference-of-functions-and-language

     

    Try using the date() function. The result was 06/15/2019 for me.

    • aztory
    • 4 yrs ago
    • Reported - view

    Entered this strig:

    if 'Service Type' = 4 and 'Miles or Months' = 2 and Item = 8 then
    today() + 365
    else

    it 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?

    • Jorg
    • 4 yrs ago
    • Reported - view

    Hi,

    please try

     

    ... then date(today() + 365)

    else....

     

    Best, Jörg

    • aztory
    • 4 yrs ago
    • Reported - view

    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!

    • Jorg
    • 4 yrs ago
    • Reported - view

    Hi,

    Where did you enter the script? In a formula field?

    Is that the complete script?

    Kind regards, Jörg

    • aztory
    • 4 yrs ago
    • Reported - view

    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

    • Sean
    • 4 yrs ago
    • Reported - view

    Make sure Number format is empty...

     

    Screen Shot 2019-06-19 at 1.40.55 PM

    • aztory
    • 4 yrs ago
    • Reported - view

    Yes sir, it is empty.

    • aztory
    • 4 yrs ago
    • Reported - view

    I see your formula is different - is that for your test?

    • Sean
    • 4 yrs ago
    • Reported - view

    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 then
    date(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.

    • aztory
    • 4 yrs ago
    • Reported - view

    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

    • Jorg
    • 4 yrs ago
    • Reported - view

    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

  • 4 yrs agoLast active
  • 12Replies
  • 3328Views