+ Reply to Thread
Results 1 to 7 of 7

Work Planner with formula for work rotations

  1. #1
    Forum Contributor sorensjp's Avatar
    Join Date
    12-23-2015
    Location
    Yokosuka, JP
    MS-Off Ver
    2007 and 2016
    Posts
    190

    Work Planner with formula for work rotations

    Alcon,

    I don't know how to modify a formula I used in EXCEL 2010 to operate in a 2007 environment. The following formula:
    =MID($AY$3,MOD(NETWORKDAYS.INTL($BA$1,E12,"0000000")-1,LEN($AY$3))+1,1) is used in the attached Excel File named ER_DIV_LIBERTY_LIBERTY TRACKER. I had modified that formula from the other attached file: rotation-schedule.xlsx under the tab Rotation_Advanced.

    I don't have a good grasp on formulas on 2007 (or 2010 & on for that matter). I appreciate any help in solving this question.

    V/r,
    Jim

    The ER_DIV_LIBERTY_LIBERTY TRACKER was based on Trevor Easton's Room Booking template and the I used Vertex42 Rotation Schedule Template to build my tracker.
    Attached Files Attached Files

  2. #2
    Forum Contributor sorensjp's Avatar
    Join Date
    12-23-2015
    Location
    Yokosuka, JP
    MS-Off Ver
    2007 and 2016
    Posts
    190

    Re: Work Planner with formula for work rotations

    Alcon,

    I made an error the modified formula was taken from the rotation-schedule.xlsx under the Rotation_String Tab. Thank you again.

    V/r,
    Jim

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,200

    Re: Work Planner with formula for work rotations

    The cells in the ER_DIV_LIBERTY_LIBERTY TRACKER file seem to be protected so I can't test on them. However I was able to modify the formula on row 13 of the rotation-schedule file using the NETWORKDAYS function so that it yielded the same results as it had using the NETWORKDAYS.INTL function, which is not supported by ver. 2007. The modified formula reads:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Notice that there is no option for weekends, which in this case doesn't seem to matter. If you need help applying the formula to the ER_DIV_LIBERTY_LIBERTY TRACKER file, then please upload a copy in which the cells in row 13 are not protected.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  4. #4
    Forum Contributor sorensjp's Avatar
    Join Date
    12-23-2015
    Location
    Yokosuka, JP
    MS-Off Ver
    2007 and 2016
    Posts
    190

    Re: Work Planner with formula for work rotations

    JeteMc,

    I entered your formula into my ER_DIV_LIBERTY_LIBERTY TRACKER file and unable to achieve the desired results. I realized that I had not explained my intended results very well. So here is my attempt at a better explanation.

    My hope is in cells E13:BH13 to reflect the text sequence in AY3:BG3 with the starting date in BA1:BF1. The current formula in the attached file allows me to show the sequence. It however seems the weekends are not seen by the NETWORKDAYS function so I think whatever character lands of Friday continues through the weekend. I don't care about weekends and holidays (being in the Navy we don't stop working).

    I really appreciate your time and effort in looking into this.

    The Password for the attached file is "Online". So please open it up and do as you please. Thank you again for your time.

    V/r,
    Jim
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,200

    Re: Work Planner with formula for work rotations

    The following formula yields the same results as those in row 13, without using the NETWORKDAYS.INTL function, so it should work in ver. 2007:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let us know if you have any questions.

  6. #6
    Forum Contributor sorensjp's Avatar
    Join Date
    12-23-2015
    Location
    Yokosuka, JP
    MS-Off Ver
    2007 and 2016
    Posts
    190

    Re: Work Planner with formula for work rotations

    JetMc,

    Thank you for your assistance. I had played around with that for a few hours. My grasp of the functions and formulas is weak at best.

    V/r,
    Jim

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,200

    Re: Work Planner with formula for work rotations

    I had applied the formula to the ER_DIV_LIBERTY_LIBERTY TRACKER file attached to post #1, not noticing that the values listed on row 13 of that file were different from the values listed on row 13 of the file of the same name attached to post #4. If you open the ER_DIV_LIBERTY_LIBERTY TRACKER file attached to post #1 and paste the formula into cell E14, then drag the fill handle over to BH14, you'll see that the values in row 14 match the values in row 13.
    Let us know if you have any questions, or if the file attached to post #4 is the one I should be attempting to match.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Hello glad to join. I work as a Planner for a lab.
    By dfriesen1 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-22-2016, 01:44 PM
  2. Work Rotation planner
    By drwats0n in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-12-2015, 12:26 PM
  3. Recipe Menu Planner macro doesn't seem to work!
    By tammack17 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-22-2013, 04:29 PM
  4. Replies: 0
    Last Post: 11-12-2012, 04:49 AM
  5. Replies: 4
    Last Post: 11-04-2011, 12:37 PM
  6. Trying to work formula for work TIME SHEET using IF formula
    By help_monique in forum Excel General
    Replies: 8
    Last Post: 10-12-2009, 11:46 PM
  7. [SOLVED] A search for $ in a formula use to work now it does not work
    By JuneJuly in forum Excel General
    Replies: 2
    Last Post: 11-30-2005, 06:15 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1