0

I'm trying to create a calculated column for a list that tracks personnel travelling.

There are two date columns in the list, one for their departure date and one for their return date.

I want a calculated column to identify their current status. Basically if neither date has passed I'd like it to say "Pending", if the departure date has passed but not the return date then "Abroad", if both dates have passed then "Complete".

I tried the below formula but keeping getting a syntax error. Can anyone see what's wrong?

=IF(AND([Departure Date] > NOW(), [Return Date] > NOW()), "Pending",
    IF(AND([Departure Date] <= NOW(), [Return Date] >= NOW()), "Abroad",
        IF(AND([Departure Date] < NOW(), [Return Date] < NOW()), "Complete", "")
    )
)
4
  • What regional settings are you using in your SPO-site? Sometime's it as simple as replacing all the commas with a semicolon. Jan 26 at 13:55
  • I did try that, but still received the syntax error
    – Matt P
    Jan 26 at 13:59
  • Do the date columns allowing time to be set or dates only? What are your site regional settings (siteadress + /_layouts/15/regionalsetng.aspx) ? Jan 26 at 14:03
  • Date columns are date only. Not sure what you mean by regional settings.
    – Matt P
    Jan 26 at 15:39

2 Answers 2

0

The formula works for me (using semicolon ";" instead of comma ",").

=IF(AND([Departure Date] > NOW(); [Return Date] > NOW()); "Pending";
    IF(AND([Departure Date] <= NOW(); [Return Date] >= NOW()); "Abroad";
        IF(AND([Departure Date] < NOW(); [Return Date] < NOW()); "Complete"; "")
    )
)

enter image description here

3
  • Still getting the same error. I noticed you add an extra parenthesis after NOW before each of the text statements. I added those and still didn't work either.
    – Matt P
    Jan 26 at 14:05
  • Your calculated column - what type of data are it set to return? Single line of text? Jan 26 at 14:17
  • Yes, single line of text
    – Matt P
    Jan 26 at 15:30
0

I tested your formula and it works fine for me here. I have my date and result columns set up as follows. enter image description here

enter image description here

enter image description here

If the problem persists, then you can try:

1.Whether all SharePoint lists have the same problem? You could create a new SharePoint list to check.

2.You could try to clear browser cache then restart it.

3.Try to use a different browser or your browser's InPrivate mode to see if the same error occurs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.