Calculating with dates
Lots of googleing showed that this is very common problem but still couldn't find a solution for my problem. Basically I have a database which tracks aircraft service bulletins. I track it by flight hours and dates. With flight hours I got it working but with dates it's complicated.
I've got these fields:
Recurrent (text) - for selecting if the bulletin is recurrent or not (Y or N)
Date (date) - date when the bulletin is applicable
PerformedC (date) - date when the bulletin was performed
IntervalY (number) - interval how often the bulletin has to be performed again (eg. 0.5 = every 6 months)
NextC (date) - calculates next date when the bulletin has to be performed
What I need is a NextC field to calculate the next maintenance date. I tried using this calculation, but it results in jibburish:
NextC = If ( Recurrent = "N" and PerformedC ≥ Date; "N/A"; If ( PerformedC = ""; Date + IntervalY * 365; PerformedC + IntervalY * 365))
The next thing I need is to calculate the number of days left to the next maintenance:
Fields are the mainly the same + LeftD. I used this calculation:
LeftD = If ( PerformedC = "" or "N/A"; NextC - Date ; NextC - PerformedC )
P.S. These calculations are correct, checked them in Excel, all it needs is to handle with dates correctly.
If anyone could help or lead me to right direction, I couldn't be more thankful.