help creating working schedules

andy14

New member
Joined
Apr 30, 2019
Messages
10
Reaction score
0
Points
0
Excel Version(s)
2016
Hello,

In my work i have to make weekly working schedules for my co-workers.

Almost all of them are part time workers and have a first main job.

I have 2 or 3 cases that they have a rotating schedule on their first job but it's always the same:

example

day 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15....
schedule A A A B B C C C F F A A A B B....

A- 08:00-16:00
B 16:00-24:00
c- 24:00-08:00
F- day off


So i know what the schedule that they will make on the other company, but i just want that to fill automaticaly on my own schedule when i insert the day of the month.


Can anyone help me with this tricky formula?

Thanks in advance
 
CROSS POST

This question has been posted in at least one other forum.
Do not post any further responses in this thread until a link has been provided to these cross posts.

Whilst we would rather you didn't cross-post, we know that there may be a valid reason for doing so. All we ask is that you provide the links in your original post to your question in the other forum(s). Read this to understand why: https://www.excelguru.ca/content.php?184

This forum is full of volunteers that give up their own time to help others, something that should be respected and not taken for granted. It’s never nice for someone to find out that a problem they have spent time solving for you has already been answered somewhere else without them knowing, and so we ask you to make it clear that you have also asked elsewhere.
Please provide the link(s). If you are unable to do so, tell us where else you have posted this query.
 

Attachments

  • Andy14-navic9965.xlsx
    9.7 KB · Views: 19
If I understood you well, use the VLOOKUP function
Please see attached file.




Thanks for the reply Navic.

The thing is what about day 16 or 2 months from now? I want to know what schedule he does on the other job without having to count days or wasting time.

what i tought was if he in 01/05/2019 is doing the A schedule, on the day_x will be =day_x - 01/05/2019 and that number will correspond to a posicion on his other schedule (A,B,C...).
 
Please attach your workbook
1st sheet set your example situation
2nd sheet same situation with expected result
 
Sorry for the late response it has been a little bit busy around here.

What i put on that excel was the type of working schedules that i do and the types of schedules my co-workers got on their other job.

What i need is a formula that returns the schedule that they make on the other job only having me to input the dates that i want to know.

Thanks for all
 

Attachments

  • example.xlsx
    21.5 KB · Views: 15
I'm sorry but I do not understand you. (Keep in mind that English is not my mother tongue). Read my previous post one more time. I asked you to list some examples of expected results. In which cell you want the formula.
What's in there is confusing for me. Date 3.5.2019 does not exist in the table above.
You should have set more examples of expected results.
 
Probably it's my English, it's not my mother language too.

The date 3/05/2019 it's on purpose, the data that we know is:

-on that day the schedule of subject 2 in the other company is A, so in the day 6/05/2019 should be B

-Subject 3 on the 03/05/2019 is F and on the day 6/05/2019 should be M


But i want excel to calculate this automatically without me to have to count days.
 
The date 3/05/2019 it's on purpose, the data that we know is:
........... so in the day 6/05/2019 should be B....
1. Why exactly 3.5.2019 date?
2. You mention the 6.5.2019 date, what does the logic of that date, has to 3.5.2019?
3. What about other dates, 7.mai, 8.mai, 9.mai etc, do you make shifts for these dates manually?
4. The difference between the two dates is 3 days, why exactly three days?
5. What is the logic of the shift schedule?
6. Does it seem to me that there should be a formula and fixed data in the range? Is there in a range of cells fixed data that you are putting the manually or all cells should contain a formula?
7. Does this mean, if a subject is free at a certain Date (ie 'F'), you give him a job at a certain time.

I do not even know what I would ask you because I do not understand your logic?
Note: Excel formula requires logic and arguments.
Look at my attachment and enter the expected results manually.
 

Attachments

  • Andy14-navic9965-2.xlsx
    33.9 KB · Views: 16
1- 3/5/2019 is a known date, it could be any date that we already know from the other schedule as it is rotating and repeating


2- The 6/05/2019 it's the date i want to know what schedule the worker does on the other company (i want to know from 6 to 12)

3- no i want excell to tell me what the worker does on the other company

4- in this case is 3 days and take me 5 min to see what is the schedule, but imagine that is 3 months, os 5 weeks from the last day that i know.

5- If i know what time he is off the other company i can plan a shift for him

6 -The cell's from subject 2 should be popullated with the schedule he is working on the other company and i would manually put his shift on mine, or at least with the hours he is not working there

7 - If he is F i can plan a schedule anytime a day but if he's in a schedule imagine from 16:00 to 00:00 i can only put him working at lunch (12:00-15:00)

If this was in programing, probably i would create a matrix and fixate a day like 3/5/2019 as a starting point and calculate the days between the start date and the date i want to know and run in the matrix to see what type of schedule it returns.
In excel i dont know how to do this.
 
Sorry, didn't put the attachment.

Either way, thanks for the try.
 

Attachments

  • Andy14-navic9965-2.xlsx
    35.2 KB · Views: 6
Sorry, didn't put the attachment.
That's something else. See attached file.
There are three examples.

Return text based on two criteria

1. The first example returns the letter (you have for each subject a unique formula, which you copy to the right to the last desired column)
For example, the 'C4' cell is the following formula. (This formula is not the same as in the 'C6' cell)
Code:
=INDEX($H$11:$H$28;MATCH(C$2;$I$11:$I$28;0))
2. The second example returns the working time. (for each subject you have a unique formula, which you copy to the right to the last desired column).
For example, the 'C4' cell is the following formula. (This formula is not the same as in the 'C6' cell)
Code:
=VLOOKUP(INDEX($H$11:$H$28;MATCH(C$2;$I$11:$I$28;0));$H$30:$I$33;2;FALSE)
3. The third example returns working time and only one formula is used. In this third example, there are helper columns. You can move them to an additional Auxiliary Sheet.
Formulas in helper columns return the fixed range for each subject.
For example, the 'C4' cell is the following formula. (select cells 'C4' and 'D4' and copy across)
Code:
=IFERROR(VLOOKUP(INDEX(INDIRECT($U2);MATCH(C$2;INDIRECT($V2);0));INDIRECT($W2);2;FALSE);"")

Subtracting working hours.

There is ambiguity on Sheet 'Folha1'.
In 'C3' cell you have working time "12:00-18:00" and the result in 'D30' cell is '6,0 hours', this is OK (18-12=6)
But
In 'O5' cell you have working time "12:00-18:00" and the result in 'P5' cell is '6,30 hours', why? (18-12=6)
Allso
In 'M3' cell you have working time "19:00-24:30" and the result in 'N3' cell is '5,0 hours', why? (24:30-19:00=5:30)

The long formula for calculating the difference hours between start/end in 'D3' cell is the following.
Code:
=IFERROR(HOUR(IF(TIMEVALUE(RIGHT(C3;5))>TIMEVALUE(LEFT(C3;5));IF(TIMEVALUE(RIGHT(C3;5))<TIMEVALUE(LEFT(C3;5));TIMEVALUE(RIGHT(C3;5))+24;TIMEVALUE(RIGHT(C3;5))-TIMEVALUE(LEFT(C3;5)));IF(TIMEVALUE(RIGHT(C3;5))<TIMEVALUE(LEFT(C3;5));TIMEVALUE(RIGHT(C3;5))+24;TIMEVALUE(RIGHT(C3;5))-TIMEVALUE(LEFT(C3;5)))-TIMEVALUE(LEFT(C3;5))))+(MINUTE(IF(TIMEVALUE(RIGHT(C3;5))>TIMEVALUE(LEFT(C3;5));IF(TIMEVALUE(RIGHT(C3;5))<TIMEVALUE(LEFT(C3;5));TIMEVALUE(RIGHT(C3;5))+24;TIMEVALUE(RIGHT(C3;5))-TIMEVALUE(LEFT(C3;5)));IF(TIMEVALUE(RIGHT(C3;5))<TIMEVALUE(LEFT(C3;5));TIMEVALUE(RIGHT(C3;5))+24;TIMEVALUE(RIGHT(C3;5))-TIMEVALUE(LEFT(C3;5)))-TIMEVALUE(LEFT(C3;5))))/60);0)

If this did not help, good luck in finding further solutions.
 

Attachments

  • Andy14-navic9965-3.xlsx
    54.1 KB · Views: 7
Man, you did it!!!!

That's it, i didn't know that index formula but the first example solved half my problem:

But i have to have a table where there is a column with the date and the other with the correspondent schedulle right?

And i have to update these columns every week or at least make a year calendar with all the working schedulles?

Could this be automatically update? Like if i enter the first day of the week it will return me the the other days?

The ambiguos data was because this was a copy of a part of the schedule and i did not update the sheet.

I will definately study this monday (my day off)

Thanks a lot
 
But i have to have a table where there is a column with the date and the other with the correspondent schedulle right?
?
And i have to update these columns every week or at least make a year calendar with all the working schedulles?
?

Could this be automatically update? Like if i enter the first day of the week it will return me the the other days?
In 'B1' cell put first day of month
In 'C2' cell put this formula
Code:
=B1+7-WEEKDAY(B1+7-2)
In 'E2', 'G2', etc cells put this formula
Code:
=C2+1, =E2+1 etc

Drop-Down Menu in 'A1' cell applied.
409 -> US
816 -> Portugal
In this cell you can choose language code for 'US' or 'Portugal' name of day (you can set white color font for this cell).

In 'C1', put formula below and copy to 'E1', etc (In these cells, the name of the day will automatically change depending on which language code you selected in the 'A1' cell)
Code:
=IF(C2<>"";TEXT(C2;"[$-"&$A$1&"]"&"dddd");"")
Please see attached file
 

Attachments

  • Andy14-navic9965-4.xlsx
    54.3 KB · Views: 8
?

?


In 'B1' cell put first day of month
In 'C2' cell put this formula
Code:
=B1+7-WEEKDAY(B1+7-2)
In 'E2', 'G2', etc cells put this formula
Code:
=C2+1, =E2+1 etc

Drop-Down Menu in 'A1' cell applied.
409 -> US
816 -> Portugal
In this cell you can choose language code for 'US' or 'Portugal' name of day (you can set white color font for this cell).

In 'C1', put formula below and copy to 'E1', etc (In these cells, the name of the day will automatically change depending on which language code you selected in the 'A1' cell)
Code:
=IF(C2<>"";TEXT(C2;"[$-"&$A$1&"]"&"dddd");"")
Please see attached file


Ok, thanks for all, tomorow i will study this, and if i have any question i will definatelly post here.
 
So after a couple of hours i manage to combine your formula of getting the schedules automaticaly on the cells with my idea (automatically find witch schedule a people is doing each day of the year.

=ÍNDICE($B$15:$B$42;CORRESP((RESTO((C2-DATA(ANO(C2);1;0)+1);28));$A$15:$A$42;0))


the 28 is the number of days the schedule runs before repeating itself (it could be ajust to other examples)

Bringing the resto funcion will show me the position of the schedule the employee does, thats why on the 28th day it's marked as 0 because the rest will also bring up 0.


Thanks for all the help
 

Attachments

  • example 3.xlsx
    18.6 KB · Views: 6
Back
Top