Date Formula to return following Monday or Wednesday

Jaime Ciabattoni

Hi everyone,

I need help with another date formula. For this one, we have a system entry that needs to be done which will trigger the "Expected live date". I currently have a "Task" column and a "Due Date" column.

Here's how it should work but realize the timing may be impossible to capture...

1) If the system entry was completed Monday, Tuesday or Wednesday by 10am ET the "Expected Live Date" should equal the following Wednesday.

2) If the system entry was completed Wednesday (10:01am or after), Thursday, or Friday by 10am ET the "Expected Live Date" should equal the following Monday.

If we can't break it down to take the time into account, I would settle for:

1) If the system entry was completed Monday or Tuesday the "Expected Live Date" should equal the following Wednesday.

2) If the system entry was completed Wednesday, Thursday, or Friday by 10am ET the "Expected Live Date" should equal the following Monday.

NOTE: I do want to make sure the WORKDAY function is included so that we can skip weekends and holidays.

Thank you in advance for your help Community!

~Jaime

JamesB

@Jaime Ciabattoni If you are willing to use row numbers in your formula as shown above just do the following.

=IF(WEEKDAY([Due Date]3) = 2, [Due Date]3 + 9, IF(WEEKDAY([Due Date]3) = 3, [Due Date]3 + 8, IF(OR(WEEKDAY([Due Date]3) = 4, WEEKDAY([Due Date]3) = 5, WEEKDAY([Due Date]3) = 6), [Due Date]3 + (7 - WEEKDAY([Due Date]3) + 2))))

You will just need to update the row number each time this occurs in your sheet.

JamesB

@Jaime This formula should take care of your request by Date.

=IF(WEEKDAY([Due Date]@row) = 2, [Due Date]@row + 2, IF(WEEKDAY([Due Date]@row) = 3, [Due Date]@row + 1, IF(OR(WEEKDAY([Due Date]@row) = 4, WEEKDAY([Due Date]@row) = 5, WEEKDAY([Due Date]@row) = 6), [Due Date]@row + (7 - WEEKDAY([Due Date]@row) + 2))))

Jaime Ciabattoni

@JamesB

Thank you! Just to make sure I understand what you did...

IF(WEEKDAY([Due Date]@row) = 2 <-- These statements are assigning values to the day of the week where Sunday equals 1. Correct?

[Due Date]@row + 2 <-- These are adding the number of days to the date. For my formula I need it to actually be the following Wednesday so if my math brain is working correctly the 2 should actually be 9? (I'll confirm when I get it into the system).

[Due Date]@row + (7 - WEEKDAY([Due Date]@row) + 2 <-- This one is a bit more confusing to me. Could you explain it? It looks like it's attached to the OR statement so just want to make sure I understand it fully.

Since you are using the WEEKDAY function, what will it do on the rare chance the date is a weekend?

~Jaime

PS I just pasted it in and am getting a CIRCULAR REFERENCE error. 🧐

JamesB

On Question 1, you are correct.

On Question 2, you are correct.

For Question 3, let me break it down.

[Due Date]@row + (7 - WEEKDAY([Due Date]@row) + 2 <-- This one is a bit more confusing to me. Could you explain it? It looks like it's attached to the OR statement so just want to make sure I understand it fully.

Because you want Wednesday, Thursday and Friday to go to the following Monday, I needed a way to determine how to get to Monday's date based on it Being a W,T,F submit date. This is what the formula does:

It looks at the Due Date, and adds a number of days in the following way.

7 is for the number of days in the Week, 7 being a Saturday. We subtract from the number 7 the weekday number of the due date.

(7 - WEEKDAY([Due Date]@row)

Wednesday being 4, so 7-4=3

Thursday being 5, so 7-5=2

Friday being 6, so 7-6=1

We take that result and add it to the Weekday of the Due Date at row to get to the Saturday for that week.

[Due Date]@row + (7 - WEEKDAY([Due Date]@row)

Wednesday being 4, so 4+3=7

Thursday being 5, so 5+2=7

Friday being 6, 6+1=7

Now that we have the Saturday Date, we add 2 to it, to get to the following Monday.

[Due Date]@row + (7 - WEEKDAY([Due Date]@row) + 2

The Reason you are getting a circular reference is because of where your formula exists in your sheet based on your screenshot.

To avoid this, you will need to have the Expected live date in a separate column with the formula I provided, so that it is not referencing within the same column.

I hope this helps.

Jaime Ciabattoni

@JamesB That does help! Thank you.

Is there any way around the circular reference? Long story short, I'm using this formula instead of predecessors because I don't have defined start and end dates. This column is supposed to represent our best estimation for all of these tasks (I'm actually going to rename the column to "Expected Date" so that is more clear).

I have a separate column the user will enter the actual completion date in, but am trying to avoid a separate column just for one date. 🤔 Any thoughts on how I might switch this up?

Here's a screenshot I put in a new post I just added where you can see the full picture...in case it helps.

~Jaime

JamesB

@Jaime Ciabattoni

I can do this with a Helper Column.

First create a Helper column named "System Entry"

Then use the formula below as a column Formula in that column.

=IFERROR(INDEX(COLLECT(CHILDREN([Due Date]@row), CHILDREN([Task Name]@row), @cell = "System Entry"), 1, 0), 0)

This will collect the System Entry date from the child row.

Then use my updated original formula below in your expected date cell in the due date column that will reference that entry to complete the date recalculation.

=IF(WEEKDAY(PARENT([System Entry]@row)) = 2, PARENT([System Entry]@row) + 9, IF(WEEKDAY(PARENT([System Entry]@row)) = 3, PARENT([System Entry]@row) + 8, IF(OR(WEEKDAY(PARENT([System Entry]@row)) = 4, WEEKDAY(PARENT([System Entry]@row)) = 5, WEEKDAY(PARENT([System Entry]@row)) =6), PARENT([System Entry]@row) + (7 - WEEKDAY(PARENT([System Entry]@row)) + 2))))

Jaime Ciabattoni

@JamesB I'm really trying to avoid using an additional helper column just for one row so I was wondering if it would be possible to repurpose an existing date cell that isn't being used for anything else. (If you can't help me make work I'll bite the bullet and add the extra column.)

I tried putting your INDEX(COLLECT) formula in the "Important Dates" column (row 38) but it was giving me #UNPARSEABLE, so just to test it I made it =[Due Date]40 (the date I need to use as the base).

I then tried to update your formula so that the [System Entry]@row was updated to [Important Dates 38]. That's giving #INVALD ARGUMENT

Then I noticed your formula included a PARENT piece which I wasn't sure if that was causing a problem so I now changed the formula to:

=IF(WEEKDAY([Important Dates]38 = 2, [Important Dates]38 + 9, IF(WEEKDAY([Important Dates]38) = 3, [Important Dates]38 + 8, IF(OR(WEEKDAY([Important Dates]38) = 4, WEEKDAY([Important Dates]38) = 5, WEEKDAY([Important Dates]38 = 6), [Important Dates]38 + (7 - WEEKDAY([Important Dates]38) + 2))))))

This is giving #INCORRECT ARGUMENT SET.

Here's a screenshot of what I'm seeing:

Of course, the possible issue with this version is that I have a template that I'm going to be cloning for each new person. However, if I have to update the [Important Dates]## I'm willing to do that if it avoids a helper column.

Thank you for your patience!!

~Jaime

PS For the sake of testing in case I needed to, I did add the help column to test what you provided. The INDEX(COLLECT) formula gave UNPARSEABLE there as well. I got this message when trying to make it a Column Formula.

JamesB

@Jaime Ciabattoni If you are willing to use row numbers in your formula as shown above just do the following.

=IF(WEEKDAY([Due Date]3) = 2, [Due Date]3 + 9, IF(WEEKDAY([Due Date]3) = 3, [Due Date]3 + 8, IF(OR(WEEKDAY([Due Date]3) = 4, WEEKDAY([Due Date]3) = 5, WEEKDAY([Due Date]3) = 6), [Due Date]3 + (7 - WEEKDAY([Due Date]3) + 2))))

You will just need to update the row number each time this occurs in your sheet.

Jaime Ciabattoni

BINGO!

Thank you for hanging on with me on that! 😍