Multiple If Statements calculating length of time.
Sorry if this question has been answered in a previous thread or seems obvious. I am new to FileMaker and have searched the forum and knowledge base without any success.
I am trying to track the length of time papers spend in review for a journal. There are three states that the paper can be in: In Production, Rejected, Under Review. I am attempting to come up with a field (Total Days) that takes these three states into consideration.
At first, I was only calculating whether the paper was under review or had entered production. The following If statement worked fine.
If ( Date Sent to Publication = "" ; Get ( CurrentDate ) - Date Submitted ; Date Sent to Publication - Date Submitted )
The problem began when I tried to add the third condition/state as a nested if statement. I tied this logic statement at first:
If ( Date Rejected = True ; Date Rejected - Date Submitted ;
If ( Date Sent to Publication = "" ; Get ( CurrentDate ) - Date Submitted ; Date Sent to Publication - Date Submitted ))
This continued to calculate the dates exactly like the first calculation. So I tried this statement:
If (Date Rejected = "" ; Date Sent to Publication - Date Submitted; If (Date Sent to Publication = "" ; Get ( CurrentDate ) - Date Submitted; Date Rejected - Date Submitted))
I don't know what I'm missing, but it really seems as if that statement should work. But all it does is disregard the Date Rejected and continue to calculate the original If statement (the nested one in this example).
What am I doing wrong? Should I use a different test? Maybe an AND or OR statement?