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

I

Israelinas

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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
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’)
 
Upvote 0
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: 14
  • Weekend.jpg
    Weekend.jpg
    52.4 KB · Views: 15
Upvote 0
Israelinas said:
explain in a picture of table:
Click to expand...
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. :)
 
Upvote 0
חוברת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)
 
Upvote 0
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"))

Book1
ABCDEFGH
1Example 1:Example 2:
2Today18/12/2020Today7/12/2020
3DateYesterday / Last Weekend IndicationIncomeDateWeekdayIncome
43/12/2020Not Last Yesterday or Last Weekend150027/11/2020Not Last Yesterday or Last Weekend1500
54/12/2020Not Last Yesterday or Last Weekend200028/11/2020Not Last Yesterday or Last Weekend2000
65/12/2020Not Last Yesterday or Last Weekend250029/11/2020Not Last Yesterday or Last Weekend2500
76/12/2020Not Last Yesterday or Last Weekend150030/11/2020Not Last Yesterday or Last Weekend1500
87/12/2020Not Last Yesterday or Last Weekend20001/12/2020Not Last Yesterday or Last Weekend2000
98/12/2020Not Last Yesterday or Last Weekend25002/12/2020Not Last Yesterday or Last Weekend2500
109/12/2020Not Last Yesterday or Last Weekend15003/12/2020Not Last Yesterday or Last Weekend1500
1110/12/2020Not Last Yesterday or Last Weekend20004/12/2020Weekend2000
1211/12/2020Not Last Yesterday or Last Weekend25005/12/2020Weekend2500
1312/12/2020Not Last Yesterday or Last Weekend15006/12/2020Weekend1500
1413/12/2020Not Last Yesterday or Last Weekend2000
1514/12/2020Not Last Yesterday or Last Weekend2500
1615/12/2020Not Last Yesterday or Last Weekend1500
1716/12/2020Not Last Yesterday or Last Weekend2000
1817/12/2020Yesterday2500
Sheet1
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:
Upvote 0
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:
GraH said:
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"))

Book1
ABCDEFGH
1Example 1:Example 2:
2Today18/12/2020Today7/12/2020
3DateYesterday / Last Weekend IndicationIncomeDateWeekdayIncome
43/12/2020Not Last Yesterday or Last Weekend150027/11/2020Not Last Yesterday or Last Weekend1500
54/12/2020Not Last Yesterday or Last Weekend200028/11/2020Not Last Yesterday or Last Weekend2000
65/12/2020Not Last Yesterday or Last Weekend250029/11/2020Not Last Yesterday or Last Weekend2500
76/12/2020Not Last Yesterday or Last Weekend150030/11/2020Not Last Yesterday or Last Weekend1500
87/12/2020Not Last Yesterday or Last Weekend20001/12/2020Not Last Yesterday or Last Weekend2000
98/12/2020Not Last Yesterday or Last Weekend25002/12/2020Not Last Yesterday or Last Weekend2500
109/12/2020Not Last Yesterday or Last Weekend15003/12/2020Not Last Yesterday or Last Weekend1500
1110/12/2020Not Last Yesterday or Last Weekend20004/12/2020Weekend2000
1211/12/2020Not Last Yesterday or Last Weekend25005/12/2020Weekend2500
1312/12/2020Not Last Yesterday or Last Weekend15006/12/2020Weekend1500
1413/12/2020Not Last Yesterday or Last Weekend2000
1514/12/2020Not Last Yesterday or Last Weekend2500
1615/12/2020Not Last Yesterday or Last Weekend1500
1716/12/2020Not Last Yesterday or Last Weekend2000
1817/12/2020Yesterday2500
Sheet1
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"))
Click to expand...
 
Upvote 0
Israelinas said:
Is it possible to combine those 2 examples into a one formula that includes both of them?
Click to expand...
It is the same formula, yes.
Israelinas said:
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.
Click to expand...
It is working on my end. (See XL2BB below)
Israelinas said:
Many thanks for your help :giggle:
Click to expand...
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"))
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Unexpected flaw in WORKDAY formula to calculate date
Replies
7
Views
328
KRice
K
C
  • Question
Dynamic calendar for parenting time
2
Replies
12
Views
354
CigarBuddha
C
S
  • Solved
WORKDAY.INTL does not block out holidays
2
Replies
13
Views
517
shawnbull
S
A
  • Question
Copy & Paste from workbook to another with criterias
Replies
2
Views
123
Anthony86
A
Ramballah
  • Question
Huge nested IF or is there a simpler way?
Replies
4
Views
180
Ramballah
Ramballah

Forum statistics

Threads
1,214,543
Messages
6,120,123
Members
448,947
Latest member
test111

Share this page

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
Back
Top