A formula for Weekend and Yesterday

[Israelinas]

Hello,

I need to create a formula that will return the following:
If today is Thursday - 23/4/2020, then I need the formula to return "Yesterday", for Wednesday 22/4/2020.
If today is Monday 27/4/2020, then I need the formula to return the the whole weekend days (Friday-Sunday - 24-26/4/2020)
I need it to return only for the last day or weekend days from the same day that I enter to the report.

Is it possible to create a formula like this? I tried a lot to use "Today, Weekday, and Workday formulas and still didn't find a way to return what I need.

Many thanks,
Salinas

 

[shaowu459]

A1=Today()
B1=IF(WEEKDAY(A1,2)=1,"Friday-Sunday - "&DAY(A1)-3&TEXT(A1-1,"-d/m/yyyy"),TEXT(A1-1,"dddd d/m/yyyy"))

try this, it won't work when Today()-3 falls into previous year.

 

[shaowu459]

B1=IF(WEEKDAY(A1,2)=1,"Friday-Sunday - "&DAY(A1-3)&TEXT(A1-1,"-d/m/yyyy"),TEXT(A1-1,"dddd d/m/yyyy"))

please ignor my previous post.

 

[etaf]

This will format the output has you want
=TEXT(TODAY()-3,"DDDD")&"-"&TEXT(TODAY()-1,"DDDD")&" - "&TEXT(TODAY()-3,"DD")&"-"&TEXT(TODAY()-1,"DD/MM/YYYY")
So now add an IF
=IF(WEEKDAY(TODAY(),2)=1, TEXT(TODAY()-3,"DDDD")&"-"&TEXT(TODAY()-1,"DDDD")&" - "&TEXT(TODAY()-3,"DD")&"-"&TEXT(TODAY()-1,"DD/MM/YYYY"),TODAY()-1)

But not sure what you want if the spreadsheet is open on a Sat or sun, at the moment will return yesterday

 

[Israelinas]

Hi,

Thank you both for help. I will try to explain in the table below:
Assumption 1: Today = 27/04/2020 (Monday)

DATE	Weekday	What I need to be presented
22/04/2020	Wednesday	"Not weekend or yesterday"
22/04/2020	Wednesday	"Not weekend or yesterday"
23/04/2020	Thursday	"Not weekend or yesterday"
23/04/2020	Thursday	"Not weekend or yesterday"
24/04/2020	Friday	"Weekend"
24/04/2020	Friday	"Weekend"
25/04/2020	Saturday	"Weekend"
26/04/2020	Sunday	"Weekend"
26/04/2020	Sunday	"Weekend"

Assumption 2: Today = 24/04/2020 (Friday)

DATE	Weekday	What I need
22/04/2020	Wednesday	"Not weekend or yesterday"
23/04/2020	Thursday	"Yesterday"
23/04/2020	Thursday	"Yesterday"

In case that "Yesterday" is a weekday, I want the formula to present "Yesterday", in case that "Yesterday" is weekend, I want the formula to present "Weekend" for all weekend days (3 days back), for all other cases - present "Not weekend or yesterday".

 

[shaowu459]

C2=IF(OR(WEEKDAY(A2,2)={5,6,7}),"Weekend",IF(A2+1=TODAY(),"yesterday","Not weekend or yesterday"))

 

[Israelinas]

C2=IF(OR(WEEKDAY(A2,2)={5,6,7}),"Weekend",IF(A2+1=TODAY(),"yesterday","Not weekend or yesterday"))

Yessss! It works! Thank you so much!