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

Occasional Visitor

# 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.

3 Replies

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

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

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

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!

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

Thank you. Actually that's the same as

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