CODEWITHSUNDEEP
archerrsa-archer-grc
PokéDev
PokéDev

Reputation: 163

Archer GRC: Formula based on setting current date between specific time frames

I have a date field which I need to use a formula in Archer.

I need to set the current date on the field which I have called DateField when a user selects a value field, ValueField, and changes to the values "Pre-Approved" or "Approved" or "Updated" if it is before 7AM, if it is after 7AM it would show the next date.

Here's what I have so far:

IF(AND(ISEMPTY([DateField]), "", IF(AND(NOT(ISEMPTY([DateField])), [???]=VALUEOF([???],"False")), DATEADD(DAY, 1, [ValueField]), ))

Upvotes: 1

Views: 900

Answers (1)

Tanveer Shaikh
Tanveer Shaikh

Reputation: 1688

From what I understand, what you want is:

ALGORITHM:

If ([ValueField] is changed to 'Pre-Approved', 'Approved' or 'Updated') AND (current time is before 7AM)
then
set [DateField] to current date

Else If ([ValueField] is changed to 'Pre-Approved', 'Approved' or 'Updated') AND (current time is after 7AM)
then
set [DateField] to (current date+1)

Make sure this formula is in the [DateField] field.

FORMULA:

IF(
AND(HOUR(NOW())<7,OR([ValueField]=VALUEOF([ValueField],"Pre-Approved"),[ValueField]=VALUEOF([ValueField],"Rejected"),[ValueField]=VALUEOF([ValueField],"Updated"))),
NOW(),
IF(
AND(HOUR(NOW())>7,OR([ValueField]=VALUEOF([ValueField],"Pre-Approved"),[ValueField]=VALUEOF([ValueField],"Rejected"),[ValueField]=VALUEOF([ValueField],"Updated"))),
DATEFORMAT(DATEADD(DAY, 1, NOW()),"M/d/yyyy")
)
)

You can add your other validations to the beginning of the first IF statement.

And thanks for making the Archer community grow on stackoverflow.

Upvotes: 2

Related Questions