if or lookup

sakrams

Board Regular
Joined
Sep 28, 2009
Messages
59
Office Version
  1. 2016
Platform
  1. Windows
Greetings Excel Gurus,

Wondering if you could help me with a formula to achieve this automatically. I am trying to generate a employee schedule and want to achieve the below.


I
X.jpg


If Employee 1 Schedule start is Sun, then lookup the day from row 2 and enter "D" if (Sun, Mon, Tue, Wed) and "X" if (Thu, Fri, Sat)
If Employee 2 Schedule start is Sat, then lookup the day from row 2 and enter "D" if (Sat, Sun, Mon, Tue) and "X" if (Wed, Thu, Fri)
If Employee 3 Schedule start is Mon, then lookup the day from row 2 and enter "D" if (Mon, Tue, Wed, Thu) and "X" if (Fri, Sat, Sun)
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Assuming ..
- all your schedules are 4 days like your samples,
- 'day' in column B is text
- dates in row 1 are actual dates and not text
- your sample results in row 6 are not correct
.. then try this copied across and down

Book1
BCDEFGHIJKLMNOPQRS
11/01/20202/01/20203/01/20204/01/20205/01/20206/01/20207/01/20208/01/20209/01/202010/01/202011/01/202012/01/202013/01/202014/01/202015/01/202016/01/2020
2WedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThu
3SunWedDXXXDDDDXXXDDDDX
4SatTueXXXDDDDXXXDDDDXX
5MonThuDDXXXDDDDXXXDDDD
6TueFriDDDXXXDDDDXXXDDD
7SunWedDXXXDDDDXXXDDDDX
Schedule
Cell Formulas
RangeFormula
D3:S7D3=IF(WEEKDAY(D$1,(SEARCH($B3,"MonTueWedThuFriSatSun")-1)/3+11)<=4,"D","X")



BTW, you will generally get faster and better help in the forum if you provide sample data in a form that can be copied for testing.
My signature block below has help with that.
 
Upvote 0
Solution
=IF(WEEKDAY(D$1,(SEARCH($B3,"MonTueWedThuFriSatSun")-1)/3+11)<=4,"D","X")

very interesting to learn from you, could you please help me to understand why need to "-1)/3+11"?
Thanks in advance
 
Upvote 0
Appears to be cross-posted here: Lookup and If (Need help find the best way to do this)

@sakrams Whilst we don't forbid cross-posting here we do require that you provide references back to your cross-posts. This is so that our members can see what has already been proposed, or indeed see whether or not your problem has been solved elsewhere. PLease make sure you do this in future.

Thanks
Jon
 
Upvote 0
could you please help me to understand why need to "-1)/3+11"?
The way that I used the SEARCH function it will return 1, 4, 7, 10, 13, 16 or 19
The WEEKDAY functions has arguments for the first day of the week including 11, 12, 13, 14, 15, 16, and 17
The arithmetic in my formula is to turn any one of the first set of numbers into the corresponding value in the second set of numbers.
For example, if the SEARCH results was 7 (the 3rd value in the first array) then the calculation in my formula is
(7-1)/3 + 11 = 13 (the 3rd value in the second array)
 
Upvote 0
Appears to be cross-posted here: Lookup and If (Need help find the best way to do this)

@sakrams Whilst we don't forbid cross-posting here we do require that you provide references back to your cross-posts. This is so that our members can see what has already been proposed, or indeed see whether or not your problem has been solved elsewhere. PLease make sure you do this in future.

Thanks
Jon

Noted. Thanks, didn't know that... Instructions will be followed for future posts..
 
Upvote 0
Assuming ..
- all your schedules are 4 days like your samples,
- 'day' in column B is text
- dates in row 1 are actual dates and not text
- your sample results in row 6 are not correct
.. then try this copied across and down

Book1
BCDEFGHIJKLMNOPQRS
11/01/20202/01/20203/01/20204/01/20205/01/20206/01/20207/01/20208/01/20209/01/202010/01/202011/01/202012/01/202013/01/202014/01/202015/01/202016/01/2020
2WedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThu
3SunWedDXXXDDDDXXXDDDDX
4SatTueXXXDDDDXXXDDDDXX
5MonThuDDXXXDDDDXXXDDDD
6TueFriDDDXXXDDDDXXXDDD
7SunWedDXXXDDDDXXXDDDDX
Schedule
Cell Formulas
RangeFormula
D3:S7D3=IF(WEEKDAY(D$1,(SEARCH($B3,"MonTueWedThuFriSatSun")-1)/3+11)<=4,"D","X")



BTW, you will generally get faster and better help in the forum if you provide sample data in a form that can be copied for testing.
My signature block below has help with that.

Thanks a million Peter, this is pure magic. I will now try to implement the solution on my actual sheet and any future request i will make sure come with proper data file and without data errors :)
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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