DATETIME Question

[Domearian]

Thought this would be simple but something just isn’t right. Using this code below:

IFS(AND(ISNOTBLANK([Time 1]),NOW()<[Time 1 DateTime Start],NOW()<[Time 1 DateTime End],[Patrol 1 Status]=“Not Completed”),CONCATENATE("Patrol 1 Due at ",[Time 1]),

AND(ISNOTBLANK([Time 1]),[Time 1 DateTime Start]<NOW(),[Time 1 DateTime End]>NOW(),[Patrol 1 Status]=“Not Completed”),“Patrol 1 Due Now”,

AND(ISNOTBLANK([Time 2]),[Time 1 DateTime Start]<NOW(),[Time 1 DateTime End]>NOW(),[Patrol 1 Status]=“Completed”),CONCATENATE("Patrol 2 Due at ",[Time 2]),
)

My Datetime is [Time 1 DateTime Start]<NOW(),[Time 1 DateTime End]>NOW() and patrol status “Note Completed” but it just is not giving the “Patrol 1 Due Now”. It’s not showing anything. Below are the start and end datetimes: I have adjusted my system dates:

All fields are DateTimes except for the text field above which is a virtual column:

I am at a loss as to why it is unable to understand what the datetimes are and what status should be set.

[Steve]

There is an extra comma at the end of your expression:

Have you shared only part of it? Please share all of it.

[Domearian]

There is yes, unfortunately that does not make a difference. So have updated the expression:

IFS(AND(ISNOTBLANK([Time 1]),[Time 1 DateTime Start]>NOW(),[Time 1 DateTime End]>NOW(),[Patrol 1 Status]=“Not Completed”),CONCATENATE("Patrol 1 Due at ",[Time 1]),

AND(ISNOTBLANK([Time 1]),[Time 1 DateTime Start]<NOW(),[Time 1 DateTime End]<NOW(),[Patrol 1 Status]=“Not Completed”),“Patrol 1 Due Now”,

AND(ISNOTBLANK([Time 2]),[Time 1 DateTime Start]<NOW(),[Time 1 DateTime End]<NOW(),[Time 2 DateTime Start]>NOW(),[Patrol 1 Status]=“Completed”),CONCATENATE("Patrol 2 Due at ",[Time 2]),

AND(ISNOTBLANK([Time 2]),NOW()>DATETIME([Time 2 DateTime Start]),NOW()<DATETIME([Time 2 DateTime End]),[Patrol 2 Status]=“Not Completed”),“Patrol 2 Due Now”
)

When I first change the expression, it looks like it will work, as you can see “Patrol 2 Due Now”, but when I save changes:

It disappears..

[Steve]

Please post a screenshot of the table’s column list showing at least the Time 1, Time 1 DateTime Start, Time 1 DateTime End, and Patrol 1 Status columns and including the column name and type for each.

How and when do Time 1 DateTime Start and Time 1 DateTime End get their values?

[Domearian]

t has to be something about the NOW() and the DateTime() of the fields. Even this basic code does not work:

IFS(
AND(ISNOTBLANK([Time 1]),NOW()>[Time 1 DateTime Start],NOW()<[Time 1 DateTime End]),“Patrol 1”,
AND(ISNOTBLANK([Time 2]),NOW()>[Time 2 DateTime Start],NOW()<[Time 2 DateTime End]),“Patrol 2”,
AND(ISNOTBLANK([Time 3]),NOW()>[Time 3 DateTime Start],NOW()<[Time 3 DateTime End]),“Patrol 3”,
AND(ISNOTBLANK([Time 4]),NOW()>[Time 4 DateTime Start],NOW()<[Time 4 DateTime End]),“Patrol 4”,
AND(ISNOTBLANK([Time 5]),NOW()>[Time 5 DateTime Start],NOW()<[Time 5 DateTime End]),“Patrol 5”,
AND(ISNOTBLANK([Time 6]),NOW()>[Time 6 DateTime Start],NOW()<[Time 6 DateTime End]),“Patrol 6”
)

System DateTime:

Patrol DateTimes

[Domearian]

It is created as part of a behaviour action. The Time 1, Time 2 etc are times of the day, Start Date Time will start with the date and then add the time, and so on. The end time is simply adding 1 (“001:00:00”) or 2 (“002:00:00”) onto the datetime start.

[Steve]

I see the dates in your spreadsheet are not in the US format (MM/DD/YYYY).

Is your spreadsheet locale set to something other than United States?

Does the table locale in the app match the locale of the spreadsheet?

[Domearian]

No, they are both set the the United Kingdom. I did think of that.

[Steve]

I’m at a loss, then. The next step is to test each individual expression component until you find the one that fails. Create separate virtual columns each with one of the following expressions:

• [Time 1]
• ISNOTBLANK([Time 1])
• NOW()
• [Time 1 DateTime Start]
• NOW()>[Time 1 DateTime Start]
• [Time 1 DateTime End]
• NOW()<[Time 1 DateTime End]

[Domearian]

I think I may have found the problem, further testing required. I had changed my System DateTime to go back to create “Night Shift”. Now that I have changed back it is sort of working. So am going to do further tests overnight in a real datetime environment.

[Domearian]

It would appear that the problem was indeed the fact that I was using a changed system datetime. The real time was about 16:00 but I manually set the system time to 22:00 the night before. When the datetime was set automatically to my location, the problems went away.