+ Reply to Thread
Results 1 to 1 of 1

streamline offset calculation, then slave that calculation to auto adjust with month

  1. #1
    Registered User
    Join Date
    10-12-2013
    Location
    Blackwater, Australia
    MS-Off Ver
    Excel 2007
    Posts
    18

    streamline offset calculation, then slave that calculation to auto adjust with month

    Hi Team,

    This spreadsheet keeps coming up, but thanks to the work of several others who have inputted into this project, this is the last hurdle.

    The offset formula below sits in E7 through to X7 and is the formula which looks for patterns over the previous 12 month period. It is looking for the following. Sick (PSL or FAM) before BLP (days off) or Annual Leave (days off). An Ex is a day where the crew is working into that day. For example they may start at 4.00pm and finish at 1.00am the following morning. This is essentially a day off where they cannot be rostered for a second shift before actual days off. This beast is also looking for the following patterns where the driver is booking off on the shift leading into the Ex. It returns the number of times any of these patterns have happened.



    =SUM(((COUNTIFS(E128:E524,"PSL",E129:E525,"EX",E130:E526,"BLP")+COUNTIFS(E128:E524,"PSL",E129:E525,"EX",E130:E526,"AL")+COUNTIFS(E128:E524,"FAM",E129:E525,"EX",E130:E526,"AL")+COUNTIFS(E128:E524,"FAM",E129:E525,"EX",E130:E526,"BLP")+COUNTIFS(E128:E524,"PSL",E129:E525,"BLP")+COUNTIFS(E128:E524,"BLP",E129:E525,"PSL"))+(COUNTIFS(E128:E524,"FAM",E129:E525,"BLP")+COUNTIFS(E128:E524,"BLP",E129:E525,"FAM"))+(COUNTIFS(E128:E524,"PSL",E129:E525,"AL")+COUNTIFS(E128:E524,"AL",E129:E525,"PSL"))+(COUNTIFS(E128:E524,"FAM",E129:E525,"AL")+COUNTIFS(E128:E524,"AL",E129:E525,"FAM"))+(COUNTIFS(E128:E524,"FAM",E129:E525,"LSL")+COUNTIFS(E128:E524,"LSL",E129:E525,"FAM"))+(COUNTIFS(E128:E524,"PSL",E129:E525,"LSL")+COUNTIFS(E128:E524,"LSL",E129:E525,"PSL")))-SUM(COUNTIFS(E128:E524,"BLP",E129:E525,"PSL",E130:E526,"AL")+(COUNTIFS(E128:E524,"BLP",E129:E525,"FAM",E130:E526,"AL")+(COUNTIFS(E128:E524,"BLP",E129:E525,"PSL",E130:E526,"LSL")+(COUNTIFS(E128:E524,"BLP",E129:E525,"FAM",E130:E526,"LSL")+(COUNTIFS(E128:E524,"LSL",E129:E525,"PSL",E130:E526,"BLP")+(COUNTIFS(E128:E524,"LSL",E129:E525,"FAM",E130:E526,"BLP")+(COUNTIFS(E128:E524,"AL",E129:E525,"FAM",E130:E526,"BLP")+(COUNTIFS(E128:E524,"AL",E129:E525,"PSL",E130:E526,"BLP"))/2))))))))



    For the sake of simplicity I will call the boldfaced portion of the algorithm Frankenstein.

    All the row numbers do is tell the computer to look between them as these represent the prior 12 months’ worth dates. If I removed them in favour of telling the computer to look at the previous 12 months of dates only then it would look like this…



    =SUM(((COUNTIFS(Insert Frankenstein - numbers here)},$D$8:$D$1294,">"&EOMONTH(TODAY()-DAY(TODAY()),-12),$D$8:$D$1294,"<="&EOMONTH(TODAY(),0)))



    Expanded I imagined it would look like this…



    =SUM(COUNTIFS(E$8:E$1294({"PSL","EX","BLP"},{"PSL","EX","AL"},{"FAM","EX","AL"},{"FAM","EX","BLP"},{"PSL","BLP"},{"BLP","PSL"},{"FAM","BLP"},{"BLP","FAM"},{"PSL","AL"},{"AL","PSL"},{"FAM","AL"},{"AL","FAM"},{"FAM","LSL"},{"LSL","FAM"},{"PSL","LSL"},{"LSL","PSL"},{"BLP",”PSL","AL"},{"BLP","FAM","AL"},{"BLP","PSL","LSL"},{"BLP","FAM","LSL"},{"LSL","PSL","BLP"},{"LSL","FAM","BLP"},{"AL","FAM","BLP"},{"AL","PSL","BLP"})/2),$D$8:$D$1294,">"&EOMONTH(TODAY()-DAY(TODAY()),-12),$D$8:$D$1294,"<="&EOMONTH(TODAY(),0)))



    While this does not give an outright error, it does not appear to recognize the calculations.

    Am I on the right track or would it be better off redesigning and streamlining Frankenstein so that it is not so clunky and then wrapping the “slave to computer clock offset” around it?

    What I am trying to achieve here is to have the entire spreadsheet row 7 automatically adjust at the beginning of each month the same as row 5 and 6.

    Copy of spreadsheet attached so you can see it in operation.

    Thank you all for your assistance once again.

    Regards

    Huntsman
    Attached Files Attached Files

+ 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. Help: Time Zone Offset Calculation
    By CBJason in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-02-2014, 12:42 PM
  2. manual calculation for one worksheet by F9 and auto calculation for other sheet
    By rayshimadri in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-22-2013, 06:16 AM
  3. Month to End Calculation
    By josros60 in forum Excel General
    Replies: 5
    Last Post: 10-31-2010, 12:32 PM
  4. Month Calculation
    By XLHead in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-07-2007, 04:30 PM
  5. Month end calculation
    By Tony in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-23-2006, 09:00 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