Page 2 of 2 FirstFirst 1 2
Results 11 to 18 of 18

Thread: help creating working schedules

  1. #11
    Seeker andy14's Avatar
    Join Date
    Apr 2019
    Posts
    10
    Articles
    0
    Excel Version
    2016


    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #12
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    884
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by navic View Post
    Look at my attachment and enter the expected results manually.
    @andy14
    I give up, I'm sorry.
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #13
    Seeker andy14's Avatar
    Join Date
    Apr 2019
    Posts
    10
    Articles
    0
    Excel Version
    2016
    Sorry, didn't put the attachment.

    Either way, thanks for the try.
    Attached Files Attached Files

  4. #14
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    884
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by andy14 View Post
    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.
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  5. #15
    Seeker andy14's Avatar
    Join Date
    Apr 2019
    Posts
    10
    Articles
    0
    Excel Version
    2016
    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

  6. #16
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    884
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by andy14 View Post
    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
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  7. #17
    Seeker andy14's Avatar
    Join Date
    Apr 2019
    Posts
    10
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by navic View Post
    ?

    ?


    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.

  8. #18
    Seeker andy14's Avatar
    Join Date
    Apr 2019
    Posts
    10
    Articles
    0
    Excel Version
    2016
    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
    Attached Files Attached Files

Page 2 of 2 FirstFirst 1 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •