Help with WORKDAY()

[Cesar_Hernande1]

Hello, could someone help me with this:
I have a creation date [FECHA DE CREACION].
I want to get a deadline [FECHA LIMITE], adding 8 hours from the creation date.
But I want the deadline to contemplate only business days from Monday to Friday and a schedule from 9:00 a.m. to 6:00 p.m.

I already tried workday, but I’m not getting what I’m looking for.

[Suvrutt_Gurjar · Accepted solution]

Please try the below expression in [FECHA LIMITE]. You could make it more compact with more testing. Please test well.

IF(TIME( [FECHA DE CREACION])<=“10:00:00”,
[FECHA DE CREACION] + “008:00:00”,
DATETIME(WORKDAY(DATE( [FECHA DE CREACION]),1) + “009:00:00”) +
(“008:00:00” - (DATETIME(DATE( [FECHA DE CREACION])-“006:00:00”) -
[FECHA DE CREACION])
)
)

[Cesar_Hernande1]

Thank you very much friend @Suvrutt_Gurjar ,
I made some modifications since if the item was generated one day I did not work, I took it as a day work, here I leave it in case it can be useful to someone.

IF(
OR(WEEKDAY(DATE([FECHA DE CREACION]))=1,WEEKDAY(DATE([FECHA DE CREACION]))=7),
DATETIME(WORKDAY(DATE([FECHA DE CREACION]),1)+“017:00:00”),
IF(
AND(AND(WEEKDAY(DATE([FECHA DE CREACION]))<>1,WEEKDAY(DATE([FECHA DE CREACION]))<>7),
TIME([FECHA DE CREACION])<“09:00:00”),DATE([FECHA DE CREACION])+“017:00:00”,
IF(
AND(TIME([FECHA DE CREACION])>=“09:00:00”,TIME([FECHA DE CREACION])<=“10:00:00”),
[FECHA DE CREACION]+“008:00:00”,
IF(
AND(TIME([FECHA DE CREACION])>“10:00:00”,TIME([FECHA DE CREACION])<=“18:00:00”),
DATETIME(WORKDAY(DATE([FECHA DE CREACION]),1)+“009:00:00”+(“008:00:00”-(DATETIME(DATE([FECHA DE CREACION])+“018:00:00”)-[FECHA DE CREACION])))
,
IF(TIME([FECHA DE CREACION])>“16:00:00”,DATETIME(WORKDAY(DATE([FECHA DE CREACION]),1)+“017:00:00”),
[FECHA DE CREACION]
)
)
)
)
)

[Suvrutt_Gurjar]

You are welcome. Thank you for posting your final expression @Cesar_Hernandez . It will certainly help anyone reading this post thread in the future.

[Steve]

Here’s your expression, reformatted to my preferences:

IF(
  OR(
    (WEEKDAY(DATE([FECHA DE CREACION])) = 1),
    (WEEKDAY(DATE([FECHA DE CREACION])) = 7)
  ),
  DATETIME(
    WORKDAY(DATE([FECHA DE CREACION]), 1)
    + “017:00:00”
  ),
  IF(
    AND(
      AND(
        (WEEKDAY(DATE([FECHA DE CREACION])) <> 1),
        (WEEKDAY(DATE([FECHA DE CREACION])) <> 7)
      ),
      (TIME([FECHA DE CREACION]) < “09:00:00”)
    ),
    (DATE([FECHA DE CREACION]) + “017:00:00”),
    IF(
      AND(
        (TIME([FECHA DE CREACION]) >= “09:00:00”),
        (TIME([FECHA DE CREACION]) <= “10:00:00”)
      ),
      ([FECHA DE CREACION] + “008:00:00”),
      IF(
        AND(
          (TIME([FECHA DE CREACION]) > “10:00:00”),
          (TIME([FECHA DE CREACION]) <= “18:00:00”)
        ),
        DATETIME(
          WORKDAY(DATE([FECHA DE CREACION]), 1)
          + “009:00:00”
          + (
            “008:00:00”
            - (
              DATETIME(
                DATE([FECHA DE CREACION])
                + “018:00:00”
              )
              - [FECHA DE CREACION]
            )
          )
        ),
        IF(
          (TIME([FECHA DE CREACION]) > “16:00:00”),
          DATETIME(
            WORKDAY(DATE([FECHA DE CREACION]), 1)
            + “017:00:00”
          ),
          [FECHA DE CREACION]
        )
      )
    )
  )
)

The test expressions of your IF() expressions are a bit redundant, so can be simplified to improve efficiency. Your inner IF() expressions test to confirm that the outer IF() expressions failed, which is unnecessary. If the inner IF() is reached, we know the outer IF() failed, so we don’t need to test again.

IF(
  OR(
    (WEEKDAY(DATE([FECHA DE CREACION])) = 1),
    (WEEKDAY(DATE([FECHA DE CREACION])) = 7)
  ),
  DATETIME(
    WORKDAY(DATE([FECHA DE CREACION]), 1)
    + “017:00:00”
  ),
  IF(
    (TIME([FECHA DE CREACION]) < “09:00:00”),
    (DATE([FECHA DE CREACION]) + “017:00:00”),
    IF(
      (TIME([FECHA DE CREACION]) <= “10:00:00”),
      ([FECHA DE CREACION] + “008:00:00”),
      IF(
        (TIME([FECHA DE CREACION]) <= “18:00:00”),
        DATETIME(
          WORKDAY(DATE([FECHA DE CREACION]), 1)
          + “009:00:00”
          + (
            “008:00:00”
            - (
              DATETIME(
                DATE([FECHA DE CREACION])
                + “018:00:00”
              )
              - [FECHA DE CREACION]
            )
          )
        ),
        IF(
          (TIME([FECHA DE CREACION]) > “16:00:00”),
          DATETIME(
            WORKDAY(DATE([FECHA DE CREACION]), 1)
            + “017:00:00”
          ),
          [FECHA DE CREACION]
        )
      )
    )
  )
)

Nested IF() expressions can often be rewritten with IFS() instead, which can improve readability and reduces parentheses:

IFS(
  OR(
    (WEEKDAY(DATE([FECHA DE CREACION])) = 1),
    (WEEKDAY(DATE([FECHA DE CREACION])) = 7)
  ),
    DATETIME(
      WORKDAY(DATE([FECHA DE CREACION]), 1)
      + “017:00:00”
    ),
  (TIME([FECHA DE CREACION]) < “09:00:00”),
    (DATE([FECHA DE CREACION]) + “017:00:00”),
  (TIME([FECHA DE CREACION]) <= “10:00:00”),
    ([FECHA DE CREACION] + “008:00:00”),
  (TIME([FECHA DE CREACION]) <= “18:00:00”),
    DATETIME(
      WORKDAY(DATE([FECHA DE CREACION]), 1)
      + “009:00:00”
      + (
        “008:00:00”
        - (
          DATETIME(
            DATE([FECHA DE CREACION])
            + “018:00:00”
          )
          - [FECHA DE CREACION]
        )
      )
    ),
  (TIME([FECHA DE CREACION]) > “16:00:00”),
    DATETIME(
      WORKDAY(DATE([FECHA DE CREACION]), 1)
      + “017:00:00”
    ),
  TRUE,
    [FECHA DE CREACION]
)

The initial OR() can be replaced with IN() to avoid computing the same value twice:

IFS(
  IN(WEEKDAY(DATE([FECHA DE CREACION])), LIST(1, 7)),
    DATETIME(
      WORKDAY(DATE([FECHA DE CREACION]), 1)
      + “017:00:00”
    ),
  (TIME([FECHA DE CREACION]) < “09:00:00”),
    (DATE([FECHA DE CREACION]) + “017:00:00”),
  (TIME([FECHA DE CREACION]) <= “10:00:00”),
    ([FECHA DE CREACION] + “008:00:00”),
  (TIME([FECHA DE CREACION]) <= “18:00:00”),
    DATETIME(
      WORKDAY(DATE([FECHA DE CREACION]), 1)
      + “009:00:00”
      + (
        “008:00:00”
        - (
          DATETIME(
            DATE([FECHA DE CREACION])
            + “018:00:00”
          )
          - [FECHA DE CREACION]
        )
      )
    ),
  (TIME([FECHA DE CREACION]) > “16:00:00”),
    DATETIME(
      WORKDAY(DATE([FECHA DE CREACION]), 1)
      + “017:00:00”
    ),
  TRUE,
    [FECHA DE CREACION]
)

My interpretation of this expression is:

1. If [FECHA DE CREACION] is a Saturday or Sunday, return 5pm of the first workday following.

Beyond here, we know [FECHA DE CREACION] is not a Saturday or Sunday.

2. If [FECHA DE CREACION] is before 9am, return 5pm of that same day.

Beyond here, we know [FECHA DE CREACION] is not a Saturday or Sunday and is not before 9am.

3. If [FECHA DE CREACION] is not after 10am, return 8 hours after [FECHA DE CREACION].

Beyond here, we know [FECHA DE CREACION] is not a Saturday or Sunday and is after 10am.

4. If [FECHA DE CREACION] is not after 6pm, return a time on the next workday equal to 9am plus 8 hours minus the time on the date of [FECHA DE CREACION] before 6pm. (I may have misrepresented that: it’s kinda difficult to explain what you’re doing, but I see what you’re trying to do, and I think you did it correctly.)

Beyond here, we know [FECHA DE CREACION] is not a Saturday or Sunday and is after 6pm.

5. If [FECHA DE CREACION] is after 4pm, return 5pm of the next workday. Note that the time on this condition is before the previous condition, so even though this condition says after 4pm, after 4pm to 6pm is handled by the previous condition. In effect, this condition only catches [FECHA DE CREACION] times after 6pm.

By here, all conditions have been handled, so the following should never happen.

6. Return the original [FECHA DE CREACION].

[Cesar_Hernande1]

Thank you very much! @Steve , you were right, your formula is much more efficient