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

Copper Contributor

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.

3 Replies

@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

@Sergei Baklan 

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!

 

@Quyet_Nguyen 

Thank you. Actually that's the same as

=7*(INT(T5/7)+Q5)-1