Return date on Friday

[mrsamjones]

I need to return a date after 30 days and if it is weekend then return Friday. It is okay if the Friday date is less than 30 days because I can be before a deadline but not after.


I have this formula but it still returns Sunday.
=if(text(A1+30,"D")="7",A1+28,if(text(A1+30,"D")="6",A1+29,A1+30))


For example, My date was 11/1/19 (A1). It returns 12/1/19 which is a Sunday. I need this date to be 11/29/19

 

[tyija1995]

I suppose something like this would work:

=TEXT(IF(TEXT(A1+30,"ddd")="Sun",A1+28,IF(TEXT(A1+30,"ddd")="Sat",A1+29,A1+30)),"mm/dd/yy")

 

[FormR]

Hi, welcome to the forum!

You can also try:

=WORKDAY(A1+31,-1)

 

[mrsamjones]

This is closer but it is putting every due date on a Friday. I only need those dates that return on a Saturday or Sunday to roll back to Friday. Thanks! I am self-teaching myself excel. I just started last week!

 

[mrsamjones]

I have tried this function but it will still return a Sunday and Saturday date. It is lie the workday is not recognizing that it the returned date is a weekend!

 

[FormR]

Hi, it works for me for your example date, do you have a specific example date where it fails?

Excel 2013/2016
	A	B
1	11/01/2019	11/29/2019
Sheet1

Cell Formulas
Range	Formula
B1	=WORKDAY(A1+31,-1)

 

[tyija1995]

This is closer but it is putting every due date on a Friday. I only need those dates that return on a Saturday or Sunday to roll back to Friday. Thanks! I am self-teaching myself excel. I just started last week!

Is this my formula or FormR's?

Mine will return Friday ONLY if 30days time is friday, saturday or sunday, otherwise it will return mon-thurs (whichever day +30 lands on)

I also tried FormR's formula and it works fine for me.

EDIT: You can check the day of a date with =TEXT(A1,"ddd")

 

[SpillerBD]

FormR's is what I normally use, however you may wish to use the WORKDAY.INTL function instead.

=WORKDAY.INTL(A1+31,-1,1)

Both can also add a list of Holidays to exclude, though you may have a different consideration if you do, Day after Holiday?

 

[mrsamjones]

Is this my formula or FormR's?

Mine will return Friday ONLY if 30days time is friday, saturday or sunday, otherwise it will return mon-thurs (whichever day +30 lands on)

I also tried FormR's formula and it works fine for me.

EDIT: You can check the day of a date with =TEXT(A1,"ddd")

I think I figured it out SpillerBD. I started with the date today, 6/4/19, it returned correctly for 07/04/19, 08/02/19, and 08/30/19. After 8/30/19, it is returning a Friday date because, 30 days after that is another weekend date. Sorry guys! Like I said, still learning! Thanks for all the help!