help creating working schedules

[andy14]

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.

[navic]

Look at my attachment and enter the expected results manually.

@andy14
I give up, I'm sorry.

[andy14]

Sorry, didn't put the attachment.

Either way, thanks for the try.

[navic]

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)

=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)

=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)

=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.

=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.

[andy14]

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

[navic]

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

=B1+7-WEEKDAY(B1+7-2)

In 'E2', 'G2', etc cells put this formula

=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)

=IF(C2<>"";TEXT(C2;"[$-"&$A$1&"]"&"dddd");"")

Please see attached file

[andy14]

?

?


In 'B1' cell put first day of month
In 'C2' cell put this formula

=B1+7-WEEKDAY(B1+7-2)

In 'E2', 'G2', etc cells put this formula

=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)

=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.

[andy14]

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