# Formula that returns "Weekend" and "Yesterday" from the day it created

#### Israelinas

##### New Member
Hello,
I have a report that I prepare on a daily basis.
Every day I present the values of the day before ("Yesterday") - compared to the whole month)
Every Monday I present the values of the weekend (from last Friday to Sunday) compared to the whole month.
I am trying to create a formula that will return "Yesterday" for all days from Tuesday to Friday in the specific week, for example today is Tuesday - so I need it to return the day before (the values for Monday - Yesterday - but not all "Monday's" in this month).
and when I am preparing the report on Monday for the weekend, so it will return the last weekend - Friday to Sunday - but not all weekend in the month - just the last one.

I have dates in my report.
Is it possible to create a formula like this?

Many thanks,

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### offthelip

##### Well-known Member
yesterday is easy:
=VLOOKUP(TODAY()-1,A5:B27,2)

#### Peter_SSs

##### MrExcel MVP, Moderator
Can you post a small set of dummy sample data with XL2BB so that we can see the layout and copy for testing and explain again in relation to that sample data?

Can you also update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using? The best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

#### Israelinas

##### New Member
Hi,

It is just about the dates - I will try to explain in a picture of table:

"Yesterday" picture shows an example that the formula should return "Yesterday".
In this example, since "Today" is 17/12/2020 and yesterday was the 16/12/20 it returns "Yesterday", for all other days it returns "Not last weekend or yesterday".

"Weekend" picture shows an example that the formula should return "Weekend".
In this example, since today theoretically is 07/12/2020 (Monday) and yesteray was Sunday which is not a workday, the formula returns "Weekend" for the last 3 days (from last Friday to Sunday).
** As you can see the 27/11/2020-29/11/2020 was also a weekend but since it is not the last weekend (today we are in 07/12/2020) then it not returns "Weekend" for them)

The formula needs to include both examples.

Many thanks,

#### Attachments

• Yesterday.jpg
73.4 KB · Views: 5
• Weekend.jpg
52.4 KB · Views: 5

#### Peter_SSs

##### MrExcel MVP, Moderator

explain in a picture of table:
Unfortunately I cannot copy from a picture to test, which is why I asked for XL2BB.

Also, the Excel version(s) you are using may determine what the best functions to use to solve this are, which is why I asked you to update you account details to show this.

#### Israelinas

##### New Member
חוברת1
BCDEFGHIJ
2Example 1:Example 2:
3Today18/12/2020Today07/12/2020
4DateWeekdayYesterday / Last Weekend IndicationIncomeDateWeekdayYesterday / Last Weekend IndicationIncome
503/12/20205Not last Yesterday or Last Weekend150027/11/20206Not last Yesterday or Last Weekend1500
604/12/20206Not last Yesterday or Last Weekend200028/11/20207Not last Yesterday or Last Weekend2000
705/12/20207Not last Yesterday or Last Weekend250029/11/20201Not last Yesterday or Last Weekend2500
806/12/20201Not last Yesterday or Last Weekend150030/11/20202Not last Yesterday or Last Weekend1500
907/12/20202Not last Yesterday or Last Weekend200001/12/20203Not last Yesterday or Last Weekend2000
1008/12/20203Not last Yesterday or Last Weekend250002/12/20204Not last Yesterday or Last Weekend2500
1109/12/20204Not last Yesterday or Last Weekend150003/12/20205Not last Yesterday or Last Weekend1500
1210/12/20205Not last Yesterday or Last Weekend200004/12/20206Weekend2000
1311/12/20206Not last Yesterday or Last Weekend250005/12/20207Weekend2500
1412/12/20207Not last Yesterday or Last Weekend150006/12/20201Weekend1500
1513/12/20201Not last Yesterday or Last Weekend2000
1614/12/20202Not last Yesterday or Last Weekend2500
1715/12/20203Not last Yesterday or Last Weekend1500
1816/12/20204Not last Yesterday or Last Weekend2000
1917/12/20205Yesterday2500
20
21** I need both examples to be in the same formula:
22** If today is Monday, then the formula will return "Weekend" for the last 3 days (Friday- Sunday), but only the last ones - not all Friday's to Sunday's
23** If today is Tuesday - Thursday, then the formula will return "Yesterday" just for the day before
גיליון1
Cell Formulas
RangeFormula
C3C3=TODAY()
H5:H14,C5:C19H5=WEEKDAY(G5)

#### Israelinas

##### New Member

Hi,
No one knows a formula that can help me with this issue?
Many thanks,

#### GraH

##### Well-known Member
Excel Formula:
``=IF(AND(WEEKDAY(\$B\$2,2)=1,\$A4>(\$B\$2-4)),"Weekend",IF(\$A4=(\$B\$2-1),"Yesterday","Not Last Yesterday or Last Weekend"))``

Cell Formulas
RangeFormula
F4:F13F4=IF(AND(WEEKDAY(\$F\$2,2)=1,\$E4>(\$F\$2-4)),"Weekend",IF(\$E4=(\$B\$2-1),"Yesterday","Not Last Yesterday or Last Weekend"))
B4:B18B4=IF(AND(WEEKDAY(\$B\$2,2)=1,\$A4>(\$B\$2-4)),"Weekend",IF(\$A4=(\$B\$2-1),"Yesterday","Not Last Yesterday or Last Weekend"))

Last edited:

#### Israelinas

##### New Member
Is it possible to combine those 2 examples into a one formula that includes both of them?
That means that the formula needs to check the day before, if today is a regular weekday, so it should return "Yesterday" to the day before, but if today is Monday, then, it should return "Weekend" for the last Friday to Sunday.
Maybe this is what you did and I missing something? because I tried to use it and for yesterday it didn't return "Yesterday".

Excel Formula:
``=IF(AND(WEEKDAY(\$B\$2,2)=1,\$A4>(\$B\$2-4)),"Weekend",IF(\$A4=(\$B\$2-1),"Yesterday","Not Last Yesterday or Last Weekend"))``

Cell Formulas
RangeFormula
F4:F13F4=IF(AND(WEEKDAY(\$F\$2,2)=1,\$E4>(\$F\$2-4)),"Weekend",IF(\$E4=(\$B\$2-1),"Yesterday","Not Last Yesterday or Last Weekend"))
B4:B18B4=IF(AND(WEEKDAY(\$B\$2,2)=1,\$A4>(\$B\$2-4)),"Weekend",IF(\$A4=(\$B\$2-1),"Yesterday","Not Last Yesterday or Last Weekend"))

#### GraH

##### Well-known Member
Is it possible to combine those 2 examples into a one formula that includes both of them?
It is the same formula, yes.
That means that the formula needs to check the day before, if today is a regular weekday, so it should return "Yesterday" to the day before, but if today is Monday, then, it should return "Weekend" for the last Friday to Sunday.
It is working on my end. (See XL2BB below)

Book1
ABC
1Example 1:
2Today29/12/2020
3DateYesterday / Last Weekend IndicationIncome
420/12/2020Not Last Yesterday or Last Weekend4000
521/12/2020Not Last Yesterday or Last Weekend4500
622/12/2020Not Last Yesterday or Last Weekend5000
723/12/2020Not Last Yesterday or Last Weekend5500
824/12/2020Not Last Yesterday or Last Weekend6000
925/12/2020Not Last Yesterday or Last Weekend6500
1026/12/2020Not Last Yesterday or Last Weekend7000
1127/12/2020Not Last Yesterday or Last Weekend7500
1228/12/2020Yesterday8000
1329/12/2020Not Last Yesterday or Last Weekend8500
1430/12/2020Not Last Yesterday or Last Weekend9000
1531/12/2020Not Last Yesterday or Last Weekend9500
Sheet1
Cell Formulas
RangeFormula
B2B2=TODAY()
B4:B15B4=IF(AND(WEEKDAY(\$B\$2,2)=1,\$A4>(\$B\$2-4)),"Weekend",IF(\$A4=(\$B\$2-1),"Yesterday","Not Last Yesterday or Last Weekend"))

Replies
6
Views
174
Replies
2
Views
91
Replies
2
Views
212
Replies
3
Views
538
Replies
5
Views
214

### Forum statistics

1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back