![]() INDEX returns an array of FALSE and TRUE so MATCH finds the first TRUE value. Subtract the hours left we need to work and you get either positive or negative numbers (or zero, hence the >=0 instead of just >0). ![]() Those values are the total work hours by the end of the first day, second day, third day, etc. The SUBTOTAL function does some magic with OFFSET and returns an array of values. The basic idea is to find the first day in the schedule when the total work hours from the start day is more than the number of hours we need to finish the production. If you expand into these columns, the formula for Days would be this: =MATCH(TRUE,INDEX((SUBTOTAL(9,OFFSET(tblSchedule])>=0,0),0)-1 That last formula is the one we really need. Hours from Start of Last Week Start in Last Week]]),2) Start Time Start Time =INDEX(tblSchedule)/2) If you were going to break it out, it might look something like this:ĭays,Start Time,Work Start Time,Hours in Last Week,Hours from Start of Last Week It can be broken into several pieces if that helps but I was trying to keep it as a nice finished product and I figured your friend wouldn't want all the extra columns in there. Note that we have to divide the sum of all work hours in a week by 2 because our schedule is two weeks and not just one.ĭays =MATCH(TRUE,INDEX((SUBTOTAL(9,OFFSET(tblSchedule)/2))),2))>=0,0),0)-1 This just converts from hours to work weeks. The only trick is that we're converting from minutes to hours because the rest of the math will use hours. Min / Unit,Qty Units,Start,Production Time (hrs),Weeks,Days,Hours,End Here's a snapshot of what I called tblProduction: (Note that my system uses the non-ISO format for dates of m/d/yyyy because America.) If you don't, this will all still work but understanding the formulas will be more difficult because it'll be a bunch of cell references instead of field names. We're going to add a few fields and I'm assuming you're using an actual table (Insert ribbon > Table). and the CSV version (time is shown as a fraction of a day): Here's a snapshot of the table I called tblSchedule: If you didn't have the schedule twice and you started on a Friday, you'd get one day and then a bunch of blank rows. #WORKING DAYS LEFT IN 2016 FULL#We needs it to be two weeks long because that way we can pick the first matching start date (like a Tuesday) and then include the next 7 rows and know that we have a full work week (like a Tuesday - Monday). Make a table that has a two week schedule. We need to figure out when the production run will be finished.We have a given start time, number of units to produce, and time to produce each unit. ![]() and assume the production line is running the entire time during that work period ![]() We will ignore down time, lunch, breaks, etc.There is a set production schedule that only occurs during certain time periods on certain days.WorkdaysSince=networkdays(dates, today(), "work. Rc=run_macro('cc', d1,d2,holidayDataset,dateColumn,p) Proc cpm data=act holidata=%sysfunc(dequote(&holidayDataset)) date=&d1 out=out interval=weekday Īctual / a_start=a_start a_finish=a_finish įunction networkdays(d1,d2,holidayDataset $,dateColumn $) Here is an alternative suggested by Radhika Kulkarni, and implemented by Liping Cai, Lindsey Puryear and Chuck Kelly. Assuming that you have a data set named USHOLIDAYS with a date column named HOLIDAYDATE, you could use the function like this: This function can read a range of holiday dates from a data set. If ( 1 < weekday (holidays )< 7 ) and (start_date <= holidays <= end_date ) then */ if ( 1 < weekday (start_date )< 7 ) thenĭiff = intck ( 'WEEKDAY', calc_start_date, end_date ) ![]() * INTCK computes transitions from one day to the next */ /* To include the start date, if it is a weekday, then */ /* make the start date one day earlier. Rc = read_array (holidayDataset, holidays, dateColumn ) Įlse put "NOTE: networkdays(): No Holiday data considered" If ( not missing (holidayDataset ) and exist (holidayDataset ) ) then * read holiday data into array */ /* array will resize as necessary */ array holidays / nosymbols * make sure the start date < end date */ Function networkdays (d1,d2,holidayDataset $,dateColumn $ ) ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |