Workday - Date formatting problem

[Ben_Dowell]

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

[TeeSee1]

Let me understand the situation.

Job Order Jobs[Start Date] is a Date column in "DD/MM/YYYY" format because of your device's locale but WORKDAY only accepts "MM/DD/YYYY" format? (actually that is the case when I test WORKDAY() with Japanese locale).

If so, would not feeding a concatenated value like CONCATENATE(MONTH([col],"/",DAY([col]),"/",YEAR([col])) to WORKDAY() work?

[Ben_Dowell]

Thank you both for your suggestions, they're really appreciated!

Unfortunately WORKDAY() won't accept a Text type value, it must be a Date type value, and concatenating also changes the type to Text.

I spotted an alternative route that I'll try;

using one virtual column to reformat the Date as MM/DD/YYYY text,
using a second VC to pull the above as a List, and set the List type to Date,
then use the second VC in WORKDAY().

I feel the List Date might revert it back to DD/MM/YYYY, but I'll give it a go!

Thanks again

[JSO]

Try formatting the date with the TEXT() function:
https://support.google.com/appsheet/answer/10107701?hl=en&sjid=11942180579966033739-EU