Forum Discussion

treloar86's avatar
treloar86
Copper Contributor
Apr 20, 2020

EXCEL - from today's date I want to find the next Friday (Every second Friday from a given date)

I'm hoping to achieve this all in one cell.

 

My crews have every second Friday off work, so if the cell can calculate what todays date is then given a specific start date it will identify the next Friday on a two week rotation.

eg

if the start date I enter is 17/04/20

then on the cell it will show a date of 01/05/20, once the 01/05/20 passes I would like the same cell to show 15/5/20, and so on.

 

I hope this makes sense.

 

I do know I can enter a date then +14 days to get the next date but this would require I whole column and it does not remove the previous dates, I really only want to see the one date which will be the next day off.

 

I could perhaps create the list off the page where it cannot be displayed but still not sure how to get only the most relevant date to be seen.

 

thank you in advance.

Excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Apr 21, 2020

    treloar86 

    If in A1 is the start date, the formula could be

    =A1+(INT((TODAY()-A1)/14)+1)*14

    For example, if in A1 is Jan 10, 2020 and today is Apr 21, formula returns May 01, 2020

    • Quyet_Nguyen's avatar
      Quyet_Nguyen
      Copper Contributor
      Aug 07, 2021

      SergeiBaklan 

      Please try this formula:

      T5+Q5*7-WEEKDAY(T5+7-6)

       

      T5: your current / ref. date: example 7thAug21

      Q5: is number of week involved:

      - Q5 = 1, you can find Friday in the same week (if t5 before Friday) or next week Friday.

      - Q5 = 2, you can find Friday in the next week (if t5 before Friday) or next 2 weeks Friday.

      Hope this is helpful for yo!

       

Resources