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

Israelinas

New Member
Joined
Dec 25, 2016
Messages
24
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,
 

Some videos you may like

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
Joined
Dec 23, 2017
Messages
1,732
Office Version
  1. 2010
Platform
  1. Windows
yesterday is easy:
=VLOOKUP(TODAY()-1,A5:B27,2)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 25, 2016
Messages
24
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
    Yesterday.jpg
    73.4 KB · Views: 5
  • Weekend.jpg
    Weekend.jpg
    52.4 KB · Views: 5

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 25, 2016
Messages
24
חוברת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
Joined
Dec 25, 2016
Messages
24

ADVERTISEMENT

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

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
707
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
What about [EDIT: corrected formula and added XL2BB]
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
Joined
Dec 25, 2016
Messages
24
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".

Many thanks for your help :giggle:
What about [EDIT: corrected formula and added XL2BB]
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
Joined
Mar 22, 2020
Messages
707
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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)
Many thanks for your help :giggle:
Glad to help.

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"))
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top