Return date on Friday

mrsamjones

New Member
Joined
Jun 4, 2019
Messages
4
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

Well-known Member
Joined
Feb 26, 2019
Messages
648
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")
 
Last edited:

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,284
Office Version
365
Platform
Windows
Hi, welcome to the forum!

You can also try:

=WORKDAY(A1+31,-1)
 

mrsamjones

New Member
Joined
Jun 4, 2019
Messages
4
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

New Member
Joined
Jun 4, 2019
Messages
4
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,284
Office Version
365
Platform
Windows
Hi, it works for me for your example date, do you have a specific example date where it fails?

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;color: #333333;;">11/01/2019</td><td style="text-align: right;color: #333333;background-color: #FFFF00;;">11/29/2019</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=WORKDAY(<font color="Blue">A1+31,-1</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
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")
 
Last edited:

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,706
FormR's is what I normally use, however you may wish to use the WORKDAY.INTL function instead.
Code:
=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

New Member
Joined
Jun 4, 2019
Messages
4
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!
 

Forum statistics

Threads
1,086,039
Messages
5,387,432
Members
402,063
Latest member
Cordeiropolis

Some videos you may like

This Week's Hot Topics

Top