Assigning Hours to Days of the Week for a Calendar

Good Morning,
First time poster so thanks in advance for your help. I am trying to build a schedule where I have a task name, hours to complete, start and finish dates. I know if you add the hours to complete to the start date I get my finish date, but I want to assign varying hours to different days of the week. Saturdays and Sundays I want to carry 8 hours per day and all weekdays only 1 hour. For example, if I have a task that takes 10 hours to complete and its starting on a Sunday, it won't finish until Tuesday (8 hours on Sunday, 1 on Monday and 1 on Tuesday). Any ideas?

Thank you

Hi

If I understood your question, I suppose you can use this (large) formula to get the final date, based on a starting date and the number of hours to complete the task, knowing that the work on Saturday and Sunday is 8 hours and only one hour on other days of the week.

Suppose start_date in B4:B_n, and Hours_to_complete in C4:C_n
Use in D2 and copy down

Formula:

=IFERROR(IFERROR(

MATCH(C4,INDEX(8*(ROUNDDOWN(C4/21,0)*7+ROW($A$1:$A$13))-7*NETWORKDAYS(B4,B4+ROUNDDOWN(C4/21,0)*7+ROW($A$1:$A$13)-1),),0),

MATCH(C4,INDEX(8*(ROUNDDOWN(C4/21,0)*7+ROW($A$1:$A$13))-7*NETWORKDAYS(B4,B4+ROUNDDOWN(C4/21,0)*7+ROW($A$1:$A$13)-1),),1)+1)

-1+B4+ROUNDDOWN(C4/21,0)*7,B4)

See the file

Jose,
Thank you so much! I think this will actually accomplish what I'm looking to do here. Can you explain what the validation is about though? I started changing the hours assigned to weekdays and once I did several of my tasks went to "bad".

Thank you

Originally Posted by José Augusto

Hi

If I understood your question, I suppose you can use this (large) formula to get the final date, based on a starting date and the number of hours to complete the task, knowing that the work on Saturday and Sunday is 8 hours and only one hour on other days of the week.

Suppose start_date in B4:B_n, and Hours_to_complete in C4:C_n
Use in D2 and copy down

Formula:

=IFERROR(IFERROR(

MATCH(C4,INDEX(8*(ROUNDDOWN(C4/21,0)*7+ROW($A$1:$A$13))-7*NETWORKDAYS(B4,B4+ROUNDDOWN(C4/21,0)*7+ROW($A$1:$A$13)-1),),0),

MATCH(C4,INDEX(8*(ROUNDDOWN(C4/21,0)*7+ROW($A$1:$A$13))-7*NETWORKDAYS(B4,B4+ROUNDDOWN(C4/21,0)*7+ROW($A$1:$A$13)-1),),1)+1)

-1+B4+ROUNDDOWN(C4/21,0)*7,B4)

See the file

Hi @alfont1120

The E: F validation columns are the way I used them to avoid errors in my formula. In my tests, I never received the "bad" validation, which meant that the date I never differed more than 7 hours (Saturday or Sunday).
I made an exhaustive validation beginning on 07-27-2019 until 12-31-2022 and did not detect any errors. The formula only detected errors by default and not by excess. I corrected the validation formula (I used ABS) and found that a multiple duration of 21 hours always translated into an error.
The correction of the formula follows.
If you continue to get errors, please send me the details of this situation.
Validation In E4... and F4...:

Formula:

=SUMIFS($O$2:$O$1286,$M$2:$M$1286,">="&B4,$M$2:$M$1286,"<="&D4)

=IF(ABS(E4-C4)<8,"Ok","Bad")

End_Date in D4:

Formula:

=IFERROR(IFERROR(

MATCH(C4,INDEX(8*(ROUNDDOWN((C4-1)/21,0)*7+ROW($A$1:$A$13))-7*NETWORKDAYS(B4,B4+ROUNDDOWN((C4-1)/21,0)*7+ROW($A$1:$A$13)-1),),0),

MATCH(C4,INDEX(8*(ROUNDDOWN((C4-1)/21,0)*7+ROW($A$1:$A$13))-7*NETWORKDAYS(B4,B4+ROUNDDOWN((C4-1)/21,0)*7+ROW($A$1:$A$13)-1),),1)+1)

-1+B4+ROUNDDOWN((C4-1)/21,0)*7,B4)

Note that validation is not necessary to solve your problem nor the table $M$2:$P$1286.

Thanks for putting so much effort into this for me, it is greatly appreciated and will definitely work for what I am trying to do.

Thanks for the kind words.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.