dateif-statementgoogle-sheetssumrounding

How to get the next Bi-weekly date based on a given start date and today's date in Google Sheets


I am trying to get bi-weekly dates based on a start date that would then provide the next upcoming date following the 14-day sequence from the start date, to get me the date that comes after today's date.

For example, if the start date is 6/15/2022, I would want the date every 14 days, and using today's date, I want to get the immediate next date that would still follow the 14-day sequence. Using today's date (6/30/2022), the next date I should get is 7/13/2022, which follows the 14-day sequence, since if you do MOD(DateDif(6/15/2022, 7/13/2022, "D"),14), you will get zero.

More Examples

Start Date: 6/15/2022
Today's Date: 6/30/2022
Next Needed Date: 7/13/2022

Today's Date: 7/14/2022
Next Needed Date: 7/27/2022

I only want to show the single next date, so I do not want to use sequence() or transpose() to fill up multiple rows/columns.

Any suggestions would be helpful, thank you.


Solution

  • try:

    =B1+(14*ROUNDUP((TODAY()-B1)/14))
    

    enter image description here