CODEWITHSUNDEEP
excel-formula
10101
10101

Reputation: 2402

Extend time depending on working days per week

I am trying to build dynamic time schedule with function, that will take in consideration how many days/week will be working days.

enter image description here

All lighter green cells are changeable, user inputs data in there. White color cell with header END is calculated. My current formula is located in Column END (it is affecting bar) and result should be:

  1. As it is 5 working days/week bar should be until Mo 18
  2. As it is 6 working days/week bar should be until We 13
  3. As it is 7 working days/week bar should be until Su 10

I don't think this is much help, but as rules are requiring to post what has been tried yet, here it is:

IF(G10>6;D10+F10-1;IF(G10<=5;(D10+F10-1)+CEILING((F10/7);1)*2;(D10+F10)+CEILING((F10/7);1)

How it should be edited to work correct?

Upvotes: 0

Views: 52

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

Assuming no holidays, you can use the following formula in E10 and copy down:

=(D10+IF(WEEKDAY(D10,2)>G10,8-WEEKDAY(D10,2),0))+F10+((ROUNDUP((F10+((WEEKDAY((D10+IF(WEEKDAY(D10,2)>G10,8-WEEKDAY(D10,2),0)),2))-1))/G10,0)-1)*(7-G10))-1

POC

In column H I used a COUNTIF function to count the number X's in order to verify the right number of days were represented.

Upvotes: 1

Related Questions