Help with Weekday formula to find the next firday

[Dennismc]

Hi all,

Thank you in advance for the help. I am working on a formula to calc the next Friday from a date.

Lets say cell a1 has Date in it, and I need to calc the following friday from 7 days out.

So if the date is a Weds 10/27/2021, I need it to find the Friday 11-5-2021

even if the date is a Friday, say 11/5/2021, I need to return the 11/12/2021 date.

I have been trying to use the following but It will show the next Friday, and I can not figure out how to advance the 7 days then find the Friday

=AC4+8-WEEKDAY(AC4+2)

Any help or direction is greatly appreciated
Dennis

 

[Fluff]

Hi & welcome to MrExcel.
How about

=WORKDAY.INTL(AC4+6,1,"1111011")

 

[Dennismc]

=WORKDAY.INTL(AC4+6,1,"1111011")

PERFECT! Thank you,

Can you if you get time, explain the function flow there, I would love to understand how this one works

 

[Fluff]

It adds 6 days to the value in AC 4 & then adds a further day taking into account weekends, which are designated by the "1111011", where 1 is true (a weekend) & 0 is false & as it runs Mon to Sun, Friday is the only workday.

 

[hrayani]

Book1
	A	B
1	DATE	NEXT FRIDAY
2	Wed, 27-Oct-21	Fri, 29-Oct-21
3	Fri, 05-Nov-21	Fri, 19-Nov-21
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=IF(WEEKDAY(A2,15)=1,A2+15-WEEKDAY(A2+2),A2+8-WEEKDAY(A2+2))

 

[Fluff]

@hrayani those are not the results, the OP is after.

 

[hrayani]

@hrayani those are not the results, the OP is after.

I got in wrong in that case

I though he is looking after next Friday when the target date is not Friday
but if a target date is Friday then he needs to advance 7 days

My bad

 

[Dennismc]

Hi & welcome to MrExcel.
How about

=WORKDAY.INTL(AC4+6,1,"1111011")

Not to be a pain, but anyway to add to check if it is a holiday to move to next business day?

 

[Fluff]

Assuming weekends are Sat & Sun, try

=WORKDAY.INTL(WORKDAY.INTL(A2+6,1,"1111011")-1,1,1,F2:F3)

Change F2:F3 to a range that holds your holidays.

 

[DesertCountry]

=IF(WEEKDAY(A1)<6,A1+8-WEEKDAY(A1+2)+7,A1+8-WEEKDAY(A1+2))

I am not an expert, but this is what I came up with. As far as checking for holidays, I have seen good videos on YouTube.