Date Formula to always return with next Friday Date?

Jen C

Hi - Any idea if you are able to add in formulas in the date field to always have the result for the next Friday? For example, if I have a date of Thursday, 3/21; I would like the formula to reflect the next Friday date, so the cell would reflect 3/29. The dates would change but it would have a formuIa in there that would calculate the next Friday's date. Is that a possibility? I know you can do it in Excel, but can I do that in SS? Thank you in advance for your help!!

Paul Newcome

What if the date is a Friday date? Would you want 1 week in the future or two weeks in the future?

Paul Newcome

@Gia Thinh That formula will not output what the poster desires. It can also be made more efficient by using

=(Date@row + (6 - WEEKDAY(Date@row))) + IF(WEEKDAY(Date@row) >= 6, 7, 0)

There will just need to be a minor tweak to the above based on the response to when we move to the next week.

@Jen C Rethinking the logic, I feel like the easiest way to explain what you are looking for (correct me if I am wrong) is actually the Friday after the next Wednesday. So basically you want to look for the next Wednesday so that you can have your Wednesday meeting and then add 2 days to get the end of whatever week the next Wednesday is in? If so, give this a try:

=(Date@row + (4 - WEEKDAY(Date@row))) + IF(WEEKDAY(Date@row) >= 4, 7, 0) + 2

Paul Newcome

What if the date is a Friday date? Would you want 1 week in the future or two weeks in the future?

Jen C

Paul Newcome - the date would have to be a Friday date. Whether if it is 1 or 2 weeks in the future, it depends on the first date in the cell. For example, 3/21 the Friday date would be 3/29. However, if the date was 3/25 it would still be 3/29. Hope that answers your question. Thank you.

Paul Newcome

I meant the starting date. What if your starting date is on a Friday. So 3/22 for instance?

Jen C

Paul Newcome - if the starting date is Friday, it would be the next Friday as the returned date. Thank you again for looking into this!

Paul Newcome

Ok. So that leaves me with just one more question...

You say "...3/21 the Friday date would be 3/29. However, if the date was 3/25 it would still be 3/29..."

Why would the Thursday date go to the following week's Friday, but the Monday date goes to the same week's Friday?

Jen C

Paul - We have an important mtg on Wed so that is why the 3/21 would go to 3/29 because the Wed mtg needs to occur. If it is 3/25, we have the Wed mtg on 3/27 so then the date would be 3/29. Thank you again!

Paul Newcome

Ok. So Saturday through Wednesday go to the very next Friday, Thursday goes to the second Friday out, and Friday just goes out one week?

Saturday = +6 days

Sunday = +5 days

Monday = +4 days

Tuesday = +3 days

Wednesday = +2 days

Thursday = +8 days

Friday = +7 days

Gia Thinh

Hi Jen,

You may want to use the WEEKDAY function in a nested IF as below:

WEEKDAY(currentdate) Returns a number representing the day of the week, 1–7, where Sunday equals 1

=IF(WEEKDAY(currentdate)=1, currentdate@row + 5, IF(WEEKDAY(currentdate)=7, currentdate@row + 6, IF(WEEKDAY(currentdate)=6, currentdate@row + 7, IF(WEEKDAY(currentdate)=5, currentdate@row + 8, IF(WEEKDAY(currentdate)=4, currentdate@row + 9, IF(WEEKDAY(currentdate)=3, currentdate@row + 10, IF(WEEKDAY(currentdate)=2, currentdate@row + 11)))))))

Hope this works for you.

Paul Newcome

@Gia Thinh That formula will not output what the poster desires. It can also be made more efficient by using

=(Date@row + (6 - WEEKDAY(Date@row))) + IF(WEEKDAY(Date@row) >= 6, 7, 0)

There will just need to be a minor tweak to the above based on the response to when we move to the next week.

@Jen C Rethinking the logic, I feel like the easiest way to explain what you are looking for (correct me if I am wrong) is actually the Friday after the next Wednesday. So basically you want to look for the next Wednesday so that you can have your Wednesday meeting and then add 2 days to get the end of whatever week the next Wednesday is in? If so, give this a try:

=(Date@row + (4 - WEEKDAY(Date@row))) + IF(WEEKDAY(Date@row) >= 4, 7, 0) + 2

MarieM

@Paul Newcome I'd like to piggy back on this thread. Could you help me with the following?

I assign reviews throughout the week, and on Wednesday 12pm, I would like to automate sending out a list of all the reviews assigned since the last Wednesday 12pm. I timestamp the date the review is assigned, but I need a formula to calculate the date of the Wednesday following that date.

We could include reviews assigned on Wednesdays with the follwing week's assignments, becuase I check a box when the review has been included in a list. So, we could just send anything that was assigned since (and including) the previous wednesday that has the "sent" box unchecked.

I think your formula above could be adapted for my case, but I don't quite understand the formula, so I'm having trouble. Thanks in advance for your help!

Paul Newcome

@MarieM We may be able to simplify this. Just use a helper checkbox column with a formula to check all rows that are within the past 7 days and do not have the manual box checked. Then set your automation to only run on Wednesday at noon.

MarieM

So, the only problem with that is that I would like the date the list is sent to appear in the list when it;s sent, so it has to be there before the list is sent?

Paul Newcome

@MarieM I'm not sure I follow. Are you able to provide a screenshot for context?

MarieM

I wanted the date the assignment is sent to replace the created date in the table that's sent. (sorry if I'm missing something very obvious!)

Paul Newcome

@MarieM Ok. I understand now. You won't be able to replace the system generated created date, but what you can do with two helper columns and an automation is this:

Insert a date type column called "Today".

Set up a record a date automation to run at 12:00am every day to record the date in the Today column.

Insert another date type column called "Sent Date" with this column formula:

=MAX(DATEONLY([Created Date]@row), Today@row)

Now… If you look at it on a Tuesday, it will show Tuesday's date. But when the automation runs on Wednesday morning and updates the Today column, it will show Wednesday's date which in turn will push through when your report sends at noon.