Working schedule in Excel

[Sinke1605]

Hello.
I am new here, and not so experienced in Excel, so I am not sure if this is possible, so I hope someone will be able to help me.
I am creating a work schedule in Excel for employees at company where I work, and I would like to do this, if possible. Work schedule is divided in colums by date of the month and in rows by the name of the employee. We have 13 different shifts of which some are valid only on working days and others only on weekends. For example: shift 1 is from 8am to 4pm valid only mon - fri, shift 2 from 10pm till 9 am next day and valid only from fri - sun, and so on. I would like for Excel to highlight for me each date in column if I miss some shift that is mandatory for that day. Foe example, if on monday we need shifts 1, 2, 3, 4 and 5, is it possible for Excel to highlight that column or specific cell at the bottom of the column if one or more of the shifts are missing? And maybe even write what shift(s) is missing?
And second question - can Excel sum working hours for each employee? Every employee will have different shift thru month. So many different shift will need to be calculated. And I need to write shift numbers, so Excel needs to read shift numbers from rows, than recognize that shift 1 is 8 working hrs, shift 2 is 11 working hrs, and then sum that and display result in some cell.
If you know this can be done please let me know with what formula or macro? If you have some specific advice it would be even better.
Thank you in advance.

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? Nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Sinke1605]

Thank you for your fast reply.
I attached an example worksheet with dummy data but real shift names, working hrs for each shift and sum of working hrs. I also attached a picture with some explanation, hope it helps.
Any question please ask. Forgot to mention in the picture shifts 12 and 13 - they are only for state holidays so it is not important to include them when looking for missing shifts.
Thank you again.

[bebo021999]

Could you use English in the file? It may help us to find out what it is

[63falcondude]

When are shifts 12 and 13 valid?

[63falcondude]

Another question:

In column E, you are showing shifts 6 and 8 in the same day but shift 6 is only valid Friday - Sunday while shift 8 is only valid Monday - Thursday.

So there would be no day where you can have both shifts 6 and 8. Am I missing something?

Also, what month is this for?

[samba_ravi]

where did you mentions the month and year in your attached file

[Sinke1605]

Shifts 12 and 13 are valid only on public holidays which is, like, 7-8 times per year, so if it is not important we can skip them. Otherwise, as I would like to use this file as a template for all months, we would have to find a way to enter public holidays in the file whenever I start a new month. I am not sure, but that seems somewhat too complicated. Correct me if I am wrong.

Shift 8 is valid Mon - Fri, so that is my mistake, sorry.
As I mentioned earlier in this message, I plan to use this file as a template, so no specific month and year. Only difference would be between months with 30 and 31 days, and February. So maybe to make a template with 31 days, and then just leave blanks or delete the extra days? This example file was made with dates and days from next month, november.
As for translation, everything is in english except name of the days in row 2. I can translate it, but will I be able to translate it back to my language afterwards? Because I need the file to be in my language.
Thank You all for your effort.

[63falcondude]

I would set your worksheet up something like this (see attachment).

Then in B3:

=SUMPRODUCT((C3:AF3=B$23:B$36)*(ABS(D$23:D$36-C$23:C$36)*24))

Still working on the missing shifts part.

[63falcondude]

Based on the layout of the worksheet in post #9:

In C15:

Please Login or Register  to view this content.

Drag the formula to the right and then down as far as needed.

Worksheet with formula is attached.

[63falcondude]

If you like the solutions posted thus far, we should be able to implement the holidays as well.

I just wanted to get the foundation built before going to that.

Let us know.

[Sinke1605]

This looks great.
It is what I am looking for. If you could just correct shift 10, it is valid mon - fri. And "go" is counted as 8 hrs shift, so it would be from 9:00 - 17:00, not 5:00.
Also, how exactly missing shift works? Because when I delete a shift that is already there (just for try) it doesn't show in missing shifts row. Also, will it work if I input a shift that is not valid for that day?
Sorry for all the questions, just trying to learn excel, so I really appreciate all your help.

[samba_ravi]

change data in cell C1:AF1 as date format from 1-11-17 to 30-11-17 and used below ARRAY FORMULA in C16
C16=IFERROR(SMALL(IF(COUNTIF(C$3:C$13,CHOOSE(MATCH(MATCH(TEXT(C$1,"ddd"),TEXT(1+{1,2,3,4,5,6,7},"ddd"),0),{1,5,6,7}),{1,2,3,7,8,10},{1,2,6,9,11},{1,2,4,6,9,11,5},{1,2,6,9,11,5}))=0,CHOOSE(MATCH(MATCH(TEXT(C$1,"ddd"),TEXT(1+{1,2,3,4,5,6,7},"ddd"),0),{1,5,6,7}),{1,2,3,7,8,10},{1,2,6,9,11},{1,2,4,6,9,11,5},{1,2,6,9,11,5})),ROWS(C$16:C16)),"")

And copy this formula across see the attache file

[63falcondude]

If you could just correct shift 10, it is valid mon - fri.

Just put an "x" in cell J34.

"go" is counted as 8 hrs shift, so it would be from 9:00 - 17:00, not 5:00.

Whoops, I forgot to write 5:00 PM as 24 hr time. Type 17:00 in cell D38.

Also, how exactly missing shift works? Because when I delete a shift that is already there (just for try) it doesn't show in missing shifts row.

Can you give me an example? Referring to the workbook shared in post #10, if I delete the 7 in cell D11, a 7 shows up in D17.

Also, will it work if I input a shift that is not valid for that day?

As it sits, there is nothing in place to flag "non-valid" shifts in C5:AF15.
If you would like, we can create some Conditional Formatting to highlight any non-valid shifts in red.

[samba_ravi]

change data in cell C1:AF1 as date format from 1-11-17 to 30-11-17 and used below ARRAY FORMULA in C16
C16=IFERROR(SMALL(IF(COUNTIF(C$3:C$13,CHOOSE(MATCH(MATCH(TEXT(C$1,"ddd"),TEXT(1+{1,2,3,4,5,6,7},"ddd"),0),{1,5,6,7}),{1,2,3,7,8,10},{1,2,6,9,11},{1,2,4,6,9,11,5},{1,2,6,9,11,5}))=0,CHOOSE(MATCH(MATCH(TEXT(C$1,"ddd"),TEXT(1+{1,2,3,4,5,6,7},"ddd"),0),{1,5,6,7}),{1,2,3,7,8,10},{1,2,6,9,11},{1,2,4,6,9,11,5},{1,2,6,9,11,5})),ROWS(C$16:C16)),"")

And copy this formula across see the attache file

in this attached file at cell c21 try below array formula and copy across for listing invalid shifts list

Please Login or Register  to view this content.

[Sinke1605]

That is strange. In worksheet from post 10 when I delete a shift, any shift, nothing happens. In fact, if some shift was already listed in the row, it just dissapear. Also, any change I made in a shift table doesn't reflect any changes on the rest of the file. Maybe something to do with my excel or some option?
If it is possible to highlight non valid shifts that would be awesome. And if I could change what shift is valid at what days, that would also be awesome but it is not mandatory as most of the shifts do not change so often.

Shift missing feature is working on worksheet from nflsales post 13, so I am not sure what am I missing? :D

[63falcondude]

The workbook is working on my end.

It sounds like you might have automatic calculations turned off. To change this (if this is the case), go to File > Options > Formulas > Workbook Calculation: Automatic > OK

To apply Conditional Formatting to C3:AF13, select C3:AF13 > Conditional Formatting > New Rule > Use a formula

=OR(AND(COUNTIF($B$23:$B$36,C3)=0,C3<>""),AND(C3<>12,C3<>13,SUMPRODUCT(($E$22:$K$22=TEXT(C$1,"dddd"))*($E$23:$K$35="")*($B$23:$B$35=C3))))

Format: Fill red > OK > OK

You can change what shifts are valid on what days by changing the location of the x's in the E23:K35 range.

The Conditional Formatting does not take shifts 12 or 13 into consideration at this time.

See attachment. I included some shift numbers that are not included in the shift range (e.g. 14) as well as invalid shift numbers (invalid for the day that they are in).

See Attachment.