Is there any way to deduce the date of the next following Friday (or any day of the week) given a particular date? For example, if a column has the date “May 5th 2021” I would want another column to find the next Friday and return “May 7th 2021”.
The formula for Nearest Friday would be:
YourDate + (6 - YourDate.Weekday())
…unless it’s already Saturday and it will show the previous one.
Correct one would be:
YourDate + Remainder(13 - YourDate.Weekday(), 7)
if it should show the same day on Friday, and
YourDate + Remainder(12 - YourDate.Weekday(), 7) + 1
if it should show the next one.
oof good catch! That would have been a bummer
Hi @Paul_Danyliuk , great catch, just some minor correction, just to get the formula right:
YourDate+Remainder(12 - YourDate.ToDateTime().Weekday(),7)+1
As in Remainder() we need to have the DateTime input
Thanks everybody! I ended up using the following:
YourDate + Remainder(7 + 6 - YourDate.ToDateTime().Weekday(), 7)
to find the next Friday (or the current date if it IS a Friday).
And then for the previous Friday (or the current date if it IS a Friday):
YourDate - Remainder(7 - 6 + YourDate .Weekday(), 7 )
In both examples the 6 can be replaced with the number of whichever day of the week you desire.
Thank you so much for looping back around with a summary of what worked for you! Many future searchers will benefit from that.
What do you think about changing the title of this post to, “Find next Friday (or Next Day of Week)”? That might be more easily found in forum searches
For me a reliable way to find the next monday | tuesday | etc is to paste in this formula and play with the Number:
Date + Remainder(7 + Number - Date.Weekday(), 7) + 1
Where Number is replaced with:
- for Monday
- for Tuesday
- for Wednesday
- for Thursday
- for Friday
- for Saturday
- for Sunday
If instead you want it to show the current day as the “next day of week”, use this formula instead:
Date + Remainder(8 + Number - Date.Weekday(), 7)
The only thing we’ve changed is the 7 became an 8 and the + 1 is removed from the end.
@Shaina_Torgerson are you able to change the title of this post so that it’s more discoverable? Something like “Find next Friday (or find next day of week)”
Dear @Connor_McCormick1 ,
Maybe @Shaina_Torgerson will be able to assist you as Steph isn’t anymore a Codan
Ah, wasn’t aware they had left that role. Great! Hi @Shaina_Torgerson
(P.S. is there a @CommunityManager handle that aliases the right person? Is that possible is Discourse?)
Using the Recurrence Rule pack:
FromText("every friday").ModifyRRule(dtstart: Today()).First()
Edit: the Relative Dates pack is even more suitable.
Absolutely @Connor_McCormick1 ! I went ahead and changed the title of the post . I’m unsure if @communitymanager handle is something we can do at this time, but I will take a look. Great suggestion!