Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Date Formula to always return with next Friday Date?
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!!
Best Answers
-
What if the date is a Friday date? Would you want 1 week in the future or two weeks in the future?
-
@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
Answers
-
What if the date is a Friday date? Would you want 1 week in the future or two weeks in the future?
-
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.
-
I meant the starting date. What if your starting date is on a Friday. So 3/22 for instance?
-
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!
-
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?
-
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!
-
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
-
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.
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech -
@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 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!
-
@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.
-
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?
-
@MarieM I'm not sure I follow. Are you able to provide a screenshot for context?
-
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!)
-
@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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…
- Can a JOIN function be used in a Sheet Summary to join two or more Sheet Summary Fields? I have two separate columns that I use a JOIN function for, but I'm not seeing how I can use it in Sheet Summar…
