Bi-Weekly date formula.

Dustin AK Lean VA

Thank you in advance for any help on this!

So, I am using this formula: "=TODAY() + (6 - WEEKDAY(TODAY()))" to show a weekly task that is always do by Friday every week. I like this one because each Sunday it updates to the current week automatically, making so that this task it is attached to shows up on the calendar for Friday of the current week.

In testing for a bi-weekly task that would be due every other Friday I tried using a similar formula: "=TODAY() + (13 - WEEKDAY(TODAY()))". Which initially seemed to work because that puts the date for every other Friday. But when Sunday comes and starts the next week this formula keeps it always at every other Friday from the current week. Instead of updating bi-weekly like I would need it to. I hope that makes sense.

Samuel Mueller

Hey @Dustin AK Lean VA. Take a quick look at this article and see if this seems to the problem. The today() formula only updates if you open and save the sheet.

NEW Help Article! Automatically update the TODAY function in formulas — Smartsheet Community

Dustin AK Lean VA

@Samuel Mueller There is no issues with them updating. I guess a better word would be when they roll over. Each week on Sunday that formula rolls over into the next week and changes the Friday to that current weeks Friday. Which is great for a weekly task. But when I tried that second formula for a bi-weekly formula, when it rolls over to the next week on Sunday it always keeps it every other Friday from the current week. Basically if my formula =TODAY() + (13 - WEEKDAY(TODAY())) was showing 8/11/23, so not this Friday but the next one, when Sunday rolls over it is changing to the next every other which would be 8/18/23. So I am wondering if there is a way to make this one not roll over until the date has past or if anyone knows a good formula to use that will show a date due on every other Friday.

Samuel Mueller

That makes sense @Dustin AK Lean VA. Have you thought of using an automation and a record a date action? Something like this would give you a perpetual 2 week date

Dustin AK Lean VA

@Samuel Mueller Looking at that option, that wont work because it will change all the dates in my due date column. Sorry I probably should have explained my sheet better. The sheet is a big task list sheet, with a good bit of tasks on it and they all have different due dates. So I have built in date formulas for all of them because some are due once a week on a specific day of the week. Some are on a specific day of the month, some task are one time task that just come in with a due date one them, and so on. The only issue I am having is trying to get a bi-weekly date formula working for a specific day of the week. In my case every other Friday. In all my searching the community pages and in testing myself I am just not having any luck lol.

JamesB

@Dustin AK Lean VA Without a constant to work against, it would be harder to formulate all of this in one cell. I would imagine that even in the bi-weekly you may have some that complete on 8/4 and then again on 8/18 and others that end on 8/11 and again on 8/25. That being the case, it is probably easiest to create yourself a helper date column, with the first Friday end date for that task and then formulate bi-weekly of off that date.

Another option is possibly adding a Drop-Down Column with task completion recurrence to generate a formula from:

Daily

Weekly

Bi-Weekly

etc.

Dustin AK Lean VA

@JamesB There actually is a start date column next to my due date column. Would that be able to be used as a constant for the bi-weekly dates? Sorry I cant take a snip or screen shot because of confidentiality reason.

JamesB

This formula will always get you to the First Friday after the start date:

=IF(WEEKDAY(Start@row) = 1, Start@row + 5, IF(WEEKDAY(Start@row) = 2, Start@row + 4, IF(WEEKDAY(Start@row) = 3, Start@row + 3, IF(WEEKDAY(Start@row) = 4, Start@row + 2, IF(WEEKDAY(Start@row) = 5, Start@row + 1, IF(WEEKDAY(Start@row) = 7, Start@row + 6, Start@row))))))

If you put this in a helper column then you can set your cadence formula from there.

nowlansavage

@Dustin AK Lean VA I came across this thread as I was trouble shooting my own solution for this Bi-weekly update issue. I came up with a solution that I think might help. You could try =IF(ISEVEN(WEEKNUMBER(TODAY())), TODAY() + (13 - WEEKDAY(TODAY())), =TODAY() + (6 - WEEKDAY(TODAY())). Using the WEEKNUMBER function along with the ISEVEN function you can create conditions that will change bi-weekly. If this creates values that are out of sync by one week you can change the function from ISEVEN to ISODD. Let me know if this helps.