Tracking RDO's 1

[RicOO1]

I am new to this web site and hope I am doing this right! I am trying to set up an Excel sheet for tracking employees “RDO’s” (Reg. Days Off) and am having a little trouble.
When A1 contains the date for the first of the month, G1 – AK1 equal the DayName and G2 – AK2 equal the Day number, ‘AND’, if, E4 contains “JS” (Sat/Sun), OR “SM” (Sun/Mon), OR “MT” (Mon/Tue), etc. the corresponding cell below the Day num. will then auto fill “RDO” (Regular Day Off). I have tried “=IF(AND(G1=”Sun”,E4=”JS”,OR(E4=”SM”,”RDO”,IF(AND(G1=”Mon”,E4=”SM”,OR(E4=”MT”,”RDO”, . . . etc.,etc.,etc. . . . ,””))))))”. I either wind up with “########” in G1 or I get the “RDO” but for the whole week and not just the actuak RDO day, and then other times when I change the date in A1 to the next month it al goes wacky. Any help out there??
Thank,
Pixiegirl1
A B C D E F G H I J K L M
1 2/1/07 EMPLOYEE LAST REG LOC- Thu Fri Sat Sun Mon Tue Wed
2 TITLE NAME FOUR SHFT RDO ATION 01 02 03 04 05 06 07
3 STSO SMITH, B. 0000 1 JS XY RDO RDO
4 STSO THOMAS, S. 4444 1 TW XY RDO RDO
5 STSO BROWN, J. 2222 2 SM RZ RDO RDO
6 LTSO VERGA, L. 3333 1 TF RZ RDO RDO
7 LTSO FERO, S. 7777 2 SM XY etc., etc., etc., etc.
8 TSO JONES, T. 5555 1 MT XY
9 TSO WALKER, F. 0505 2 WT RZ
10 TSO WALKER, J. 9119 2 JS RZ
11 TSO MARK, P. 4140 2 SM XY
12

 

[SkipVought]

Hi,

For an application like this to work seamlessly for ANY month, the VALUES in G1:AK1 must be dates--the first date referenced to A1 and each data after that a formula, adding one to the previous date. Format these cells d fo display DAY of the date.

G2:AK2 references the row 1 value and is formatted ddd to display three character day of date.

Now you build a table that decodes the days, the data range name is RDO_TABLE
[tt]
RDO FR TH
JS 0 1
SM 1 2
MT 2 3
TW 3 4
WT 4 5
TF 5 0
FS 6 1
[/tt]
Here's the RDO formula
[TT]
=IF(OR(VLOOKUP($E4,RDO_TABLE,2)=MOD(G$2,7),VLOOKUP($E4,RDO_TABLE,3)=MOD(G$2,7)),"RDO","")
[/TT]


Skip,
[sub]
[red][/red]
[/sub]

 

[RicOO1]

Skip,

I'm not well versed in building tables, does it go somewhere on the same sheet or a new sheet and how do you set the data range name? Sorry for my ignorance but I guess that is why these type of web sites are so popular and useful.

Pixiegirl1

 

[SkipVought]

The table can be on any sheet. Typically, I put lookup tables on a separate sheet designated for that purpose.

1. Select the DATA range in the lookup table (exclude the headings)

2. In the Name box enter RDO_TABLE and hit ENTER.

The Name box is to the left of the Formula Bar. If your Formula Bar is not visible, Tools/Options - View tab - check Show Formula bar

Check HELP on the VLOOKUP function in order to understand what's happening.



Skip,
[sub]
[red][/red]
[/sub]

 

[RicOO1]

Skip,

Thank you very much for your help. After a few trial and errors (errors on my part) I got your suggestion to work and I sure appreciate your assistance. I KNOW that I will have other questions about Excel in the future and I sure hope you are still around and willing to help. You are doing a great service for those of us who have not had the opportunity to learn as much as others, some are made to learn and some are made to teach!

Thanks again,

Pixiegirl

 

[SkipVought]

Pixiegirl,

I have been an enthusiastic Excel user for about 15 years. Yes, I know quite a bit about some of Excel's capabilities, but there are areas where I know next to nothing! I continue to LEARN; in fact, I learned something new today, hear at Tek-Tips, from several other colleagues around the world.

You can be assured that someone will be at the ready to offer a tip or solution. And some day, I'd hope that you would feel enough growing assurance in your skills and abilities, to begin offing your contributions here as well.

Skip,
[sub]
[red][/red]
[/sub]