Table that matches work shift hours to work shift codes

Hi Iliop,

Welcome to the board.

This should take care of the hours part of the table. In B1 enter this formula and fill across / copy across to Y1

Formula:

Please Login or Register  to view this content.

Apply formatting as h:mm AM/PM.

I cannot follow requirements for the rest of the table, and I have not come up with anything that duplicates posted results, and I am at a loss to know what questions to ask. Can you elaborate? Any formula I come up with recognizes the shift code names. So every cell gets filled with something which is not what you want. Do you have another table that lists each shift code name with their valid work(?) hours?

Edit I modified the formula to wrap around midnight. Before it incremented to the next day at midnight.

Hello Dave and thanks for the response. As i feared, i haven't explained this properly it seems.

B1 to Y1 should be static, they just show the time of the day, from 6 am today to 5 am tomorrow.

What i want, is that when the user enters one of the 24 possible shift codes on the A column, the boxes corresponding to the times of that shift code get a "1" under them.
So, for example: If the user adds someone working the Π2 shift on the A4 box, then the boxes D4 to K4 should receive a "1" indicator while the rest of the 4th row remains blank. Then if he also has another person coming in one hour after the first, he will enter the Π3 shift code on A5, resulting in the boxes E5 to L5 getting the "1" indicator, leaving the rest of the 5th row blank. The sum at the end will let the user know that he will have one active member at 8 am and 4 pm and 2 active members in the hours between these 2.

The code names they use for their shifts are fairly easy. They have 3 zones marked with the letters Π for the morning one, Α for the afternoon and Ν for the night. Their day begins at 6 am and Π is the first shift (6 am to 2 pm), then Π1 (7 am to 3 pm), Π2 (8 am to 4 pm) etc until Π7 (1 pm to 9 pm). A is 2 pm to 10 pm, then A1 (3 pm to 11 pm) etc until A7 (9 pm to 5 am). Finally N marks the shift from 10 pm to 6 am and it follows as before until N7 which is 5 am to 1 pm.

I presume , in order to get the result i want, i need some sort of formula under the hours that will read the corresponding A-column entry and if it sees one of the names that fit that time it will give it the "1" indicator. So for example, in order for the B2 box to trigger the "1" indicator , it would need to read on the A2 column a name that includes that time, in this case N1-7 and Π. Anything else would leave that box blank.

I hope it's a bit better explained now

Apologies,

Alex

Iliop,

I'm working on something that shows promise, however I am running into difficulties reconciling the Greek character set with American. This may take time.

Iliop,

I am uploading what I have so far. It includes a lookup table for shift hours in AD2:AF25. The formula I use in C2:Y23 is

Formula:

Please Login or Register  to view this content.

It works for any shift beginning with "Π". "N" and "A" fails.

There are temporary "check" columns in A2:A7 (an inserted temporary column) and in AC2:AC25 that demonstrate the problem.

I am going to see if I can get us some community help on this one.

A listing of the 24 codes would be an asset in helping you.

I have advanced this down to N2 but have run out of time right now.
Formula in Sheet1!B2

Formula:

Please Login or Register  to view this content.

Lookup table on Sheet2

	A	B	C	D
2	Π	6:00	πμ	14:00
3	Π1	7:00	πμ	15:00
4	Π2	8:00	πμ	16:00
5	Π3	9:00	πμ	17:00
6	Π4	10:00	πμ	18:00
7	Π5	11:00	πμ	19:00
8	Π6	12:00	μμ	20:00
9	Π7	13:00	μμ	21:00
10	Α	14:00	μμ	22:00
11	Α1	15:00	μμ	23:00
12	Α2	16:00	μμ	0:00
13	Α3	17:00	μμ	1:00
14	Α4	18:00	μμ	2:00
15	Α5	19:00	μμ	3:00
16	Α6	20:00	μμ	4:00
17	Α7	21:00	μμ	5:00
18	Ν	22:00	μμ	6:00
19	Ν1	23:00	μμ	7:00
20	Ν2	0:00	πμ	8:00
21	Ν3	1:00	πμ	9:00
22	Ν4	2:00	πμ	10:00
23	Ν5	3:00	πμ	11:00
24	Ν6	4:00	πμ	12:00
25	Ν7	5:00	πμ	13:00

OK, this isn't as elegant as I would like but it works. The shaded area of the worksheet can/should be hidden. I have not hidden it so that you could see what is going on.
In B2 enter this formula and copy across to AF2 then fill down.

Formula:

Please Login or Register  to view this content.

Please note that the codes given will NOT work if typed in. I should have mentioned before that the codes are not reproducible in any way other than copying and pasting them. I assume that these codes are produced by a computer set up for the Greek language. When I used the CODE function to determine what the character code was for these characters and then used the CHAR function to try and reproduce the character. Failure was the name of the game. So copying and pasting was how I got to be able to use the codes given.

Another thing about the times given in the code list looked the same as in the Excel worksheet but they were different in a lot of cases so I created the times in the worksheet and then used the same formula to create the times. There must have been some very small differences in the serial numbers that didn't show after formatting was applied to show the times until I did this.

@newdoverman
Thanks for covering all that.

The time formula thing makes better sense than what I had originally thought .... that being the additional column in the lookup table. That apparently isn't an issue at all.

Until I made absolutely sure that the times matched everywhere, as I filled the formula across and down, suddenly the values jumped a column to the right. This happened in a random manner so I just made sure that the times were calculated identically.