WORKDAY() not accepting negative days offset argument

[timsimpson]

I seem to have hit a wall with WORKDAY(). 

I've a [deadline] column of 'date' type and a [workshop days] column of 'number' type

I'm looking to create a [start date VC] using the expression WORKDAY([deadline],-[workshop days]) and I'm getting  'the added or subtracted value results in an un-representable datetime'.

It seems to be the negative second argument causing the issue. 

The following arguments get no error and return the expected result:

WORKDAY([deadline],[workshop days]) 

WORKDAY([deadline],5)

WORKDAY(TODAY(),-5)

But...

WORKDAY([deadline],-5) returns the same error.

I'm stumped but am wondering whether it's something to do with my using UK date format in the [deadline] column -  DD/MM/YYYY. Any ideas?

[Gustavo_Eduardo]

From what I'm seeing in the Workday(), it seems that you have no error in the expression, which still leaves me perplexed. Can you check that the value -5 is a value of type number() and not text

[timsimpson]

The [workshop days] column is Number type (not decimal) and the [deadline] is Date type (not datetime). Thanks for checking!

[Suvrutt_Gurjar]

@Gustavo_Eduardo ]s suggestion is valid. You may want to recheck column types because typically 

WORKDAY([deadline],-5) should also work if [deadline] is a date type column but you mentione you are getting an error.

---

@timsimpson wrote:

But...

WORKDAY([deadline],-5) returns the same error.

---

Also please try with the below expression if it works

WORKDAY([deadline],  0-[workshop days]) 

[timsimpson]

Thanks for the sugestions but I'm getting the same error. I also tried WORKDAY([deadline],  [workshop days]*-1) but that didn't work either. 

Interestingly,  WORKDAY(TODAY(), -[workshop days]) is not showing an error but is returning a workday +[workshop days] in the future, not the past. 

The formula will not accept WORKDAY("30/3/2025", -[workshop days]), with the date expressed in UK format, as it doesn't recognise it as a date but as text , but it will accept WORKDAY("3/30/2025", -[workshop days]) ; US date format. A US/UK date formatting issue seems to be part of the issue. 

So I tried

WORKDAY(DATE([deadline]), -[workshop days])

This didn't return an error but instead returned a workday +[workshop days ] in the future.

But all the following result in an 'unrepresentable datetime' error:

       WORKDAY(DATE([deadline]), 0-[workshop days])

       WORKDAY(DATE([deadline]), [workshop days]*-1)

       WORKDAY(DATE([deadline]), -5)

Then, final check:

DATE([deadline])-5

This behaves as expected.

So as far as I can ascertain there is an issue with the WORKDAY() function. Any suggestions gratefully received.

[Gustavo_Eduardo]

Please contact support. You must grant them access when they request it and wait for a response.

[Suvrutt_Gurjar]

You may still want to check your column types. is [deadline] date or datetime type column. Also some screenshots ( of errors, column settings )always help

The following expression perfectly works for me in testing. 

The expression is : WORKDAY([Deadline], 0-[Workshop_Days])

The column types are as follows

And finally the Start_Date column settings

[timsimpson]

Thanks @Suvrutt_Gurjar for persevering.

I too have created a standalone app to test the formula which appears to return the expected date:

Which leaves me just as confused. Here are a couple of screengrabs from the troublesome app. Maybe I'm being blind to something:

So I'm stumped. 

[Suvrutt_Gurjar]

Yes, you may want to contact support team for this.

In general, yes, there appears to be one setting that could be causing an issue. We could possibly troubleshoot it by exchanging messages/ screenshots in the community. However it would be faster if someone takes a look at the app.

One thing you may want to check is dates are in uniform format ("dd/mm/yyyy" since you are using UK date format ) all across the app. Does backend also store dates in the "dd/mm/yyyy" format? In general, yes it sounds that it has to something with date format somewhere in the date capture and saving workflow.

[timsimpson]

Thanks for looking into it. Much appreciated. I'll contact support. 

[timsimpson · Accepted solution]

RESOLVED - The issue was due to blank cells in the [deadline] column. 

IF(isnotblank([Deadline]),WORKDAY([deadline],0-[Workshop days]),"")

With thanks to Appsheet support.

[Gustavo_Eduardo]

Thanks for sharing the solution and clearing up the error.

[Suvrutt_Gurjar]

Thank  you for the update. Good to know that suggestion to use 

0-[Workshop days]

as second argument of the WORKDAY() function was used in the final solution.