Hi everyone,
Hopefully someone can help me with this problem, as I'm running out of ideas!
To put it simply, WORKDAY() only evaluates MM/DD/YYYY, and I need it to accept a DD/MM/YYYY Date value.
A quick way to test is;
WORKDAY("21/04/2024", -1) - Invalid, it sees it as text
WORKDAY("04/21/2024", -1) - Valid
To elaborate, I have a table of tasks with start dates and required by dates. Some of them are related to each other in a parent table, and the related task dates must follow on from one another.
ie.
Task A:
Start Monday 9am
Finish Tuesday 5pm
Task B:
Start Wednesday 9am
Finish Wednesday 5pm
Task C:
Start Thursday 9am
etc...
The dates aren't virtual columns as it reduced performance and the app is used on mobile.
If the last task's required date changed an automation triggers an action to set the required dates that preceed it. Here's the action formula (my apologies for the poor choice of column names, that wasn't me!):
DATETIME(
WORKDAY(DATE(MIN(
SELECT(
Job Order Jobs[Start Date],
AND(
[Job Order ID] = [_THISROW].[Job Order ID],
[Job] = "7168ef58",
[Status] <> "Completed"
)
)
)), - 1)
+ ("17:00:00" - "00:00:00"))
If a date could be valid in either format such as 12/04/2024, its not a problem and it outputs 12 April 2024 as intended. If the date is only valid in one format such as today 21/04/2024, the result is blank.
I've tried converting the date to Text() and reformatting to MM/DD/YYYY but it doesn't accept a Text value.
I've then tried converting it back again with Date(), but it then reverts to a DD/MM/YYYY Date value.
Thank you very much!!
Ben
LinkedIn
Twitter
