Calculate task date by skipping non-working time.

[rockyhawkky]

I am making a task tracking grant chart using 2 input value Start(Date and time) and Duration(Hourly), to calculate their Finish by simply just Start+Duration/24
as you can see in this sheet.

Book1
	A	B	C	D
1	Task	Duration	Start	Finish
2	Task1	12	01-Oct 06:00	01-Oct 18:00
3	Task2	13	01-Oct 06:00	02-Oct 07:00
Sheet1

Cell Formulas
Range	Formula
D2	D2	=C2+(B2/24)

my problem is the working time is 06:00-18:00 (im using 24 hr format) any time before or after this time is non-working time and i want my finish date to calculated by skipping these period.
ex. if my start date is 1-Oct 6:00 duration is 13 finish will go to next day 2-Oct 7:00 as you can see in task 2
any idea of formular to cal my Finish date?

 

[vladimiratanasiu]

I am making a task tracking grant chart using 2 input value Start(Date and time) and Duration(Hourly), to calculate their Finish by simply just Start+Duration/24
as you can see in this sheet.

Book1
	A	B	C	D
1	Task	Duration	Start	Finish
2	Task1	12	01-Oct 06:00	01-Oct 18:00
3	Task2	13	01-Oct 06:00	02-Oct 07:00
Sheet1

Cell Formulas
Range	Formula
D2	D2	=C2+(B2/24)

my problem is the working time is 06:00-18:00 (im using 24 hr format) any time before or after this time is non-working time and i want my finish date to calculated by skipping these period.
ex. if my start date is 1-Oct 6:00 duration is 13 finish will go to next day 2-Oct 7:00 as you can see in task 2
any idea of formular to cal my Finish date?

Try this. Use some aditional cells, to define the start and the end time of the working period.

Book1
	A	B	C	D	E	F
1	Start	Hours / Duration	Finish		Begin	End
2	01-Oct 06:00	12	02-Oct 06:00		06:00:00	18:00:00
3	01-Oct 06:00	13	02-Oct 07:00
4	02-Oct 06:00	27	04-Oct 09:00
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=WORKDAY(A2,INT(B2/12)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,12),MOD(MOD(B2,12),1)*60,0)>$F$2,1,0))+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,12),MOD(MOD(B2,8),1)*60,0)>$F$2,$E$2+TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,12),MOD(MOD(B2,12),1)*60,0)-$F$2,TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,12),MOD(MOD(B2,12),1)*60,0))

 

[awoohaw]

try this, maybe?:

Mr excel questions 39.xlsm
	A	B	C	D	E
1	Task	Duration	Start	Finish
2	Task1	12	10-01-2023 06:00	10-01-2023 18:00	10-01-2023 18:00
3	Task2	13	10-01-2023 06:00	10-02-2023 07:00	10-02-2023 07:00
4		1	10-01-2023 01:00		10-01-2023 02:00
5		2	10-01-2023 02:00		10-01-2023 04:00
6		3	10-01-2023 03:00		10-01-2023 06:00
7		4	10-01-2023 04:00		10-01-2023 08:00
8		5	10-01-2023 05:00		10-01-2023 10:00
9		6	10-01-2023 06:00		10-01-2023 12:00
10		7	10-01-2023 07:00		10-01-2023 14:00
11		8	10-01-2023 08:00		10-01-2023 16:00
12		9	10-01-2023 09:00		10-01-2023 18:00
13		10	10-01-2023 10:00		10-02-2023 08:00
14		11	10-01-2023 11:00		10-02-2023 10:00
15		12	10-01-2023 12:00		10-02-2023 12:00
16		13	10-01-2023 13:00		10-02-2023 14:00
17		14	10-01-2023 14:00		10-02-2023 16:00
18		15	10-01-2023 15:00		10-02-2023 18:00
19		16	10-01-2023 16:00		10-02-2023 20:00
20		17	10-01-2023 17:00		10-02-2023 22:00
21		18	10-01-2023 18:00		10-03-2023 00:00
22		19	10-01-2023 19:00		10-03-2023 02:00
23		20	10-01-2023 20:00		10-03-2023 04:00
24		21	10-01-2023 21:00		10-03-2023 06:00
25		22	10-01-2023 22:00		10-03-2023 08:00
26		23	10-01-2023 23:00		10-03-2023 10:00
27		24	10-01-2023 00:00		10-02-2023 12:00
rockyhawkky

Cell Formulas
Range	Formula
D2	D2	=C2+(B2/24)
B4:B27	B4	=SEQUENCE(24)
C4:C27	C4	=DATE(2023,10,1)+TIME(B4,0,0)
E2:E27	E2	=IF(18/24-MOD(C2,1)<B2/24,12/24,0)+B2/24+C2
Dynamic array formulas.

 

[awoohaw]

try this, maybe?:

Mr excel questions 39.xlsm
	A	B	C	D	E
1	Task	Duration	Start	Finish
2	Task1	12	10-01-2023 06:00	10-01-2023 18:00	10-01-2023 18:00
3	Task2	13	10-01-2023 06:00	10-02-2023 07:00	10-02-2023 07:00
4		1	10-01-2023 01:00		10-01-2023 02:00
5		2	10-01-2023 02:00		10-01-2023 04:00
6		3	10-01-2023 03:00		10-01-2023 06:00
7		4	10-01-2023 04:00		10-01-2023 08:00
8		5	10-01-2023 05:00		10-01-2023 10:00
9		6	10-01-2023 06:00		10-01-2023 12:00
10		7	10-01-2023 07:00		10-01-2023 14:00
11		8	10-01-2023 08:00		10-01-2023 16:00
12		9	10-01-2023 09:00		10-01-2023 18:00
13		10	10-01-2023 10:00		10-02-2023 08:00
14		11	10-01-2023 11:00		10-02-2023 10:00
15		12	10-01-2023 12:00		10-02-2023 12:00
16		13	10-01-2023 13:00		10-02-2023 14:00
17		14	10-01-2023 14:00		10-02-2023 16:00
18		15	10-01-2023 15:00		10-02-2023 18:00
19		16	10-01-2023 16:00		10-02-2023 20:00
20		17	10-01-2023 17:00		10-02-2023 22:00
21		18	10-01-2023 18:00		10-03-2023 00:00
22		19	10-01-2023 19:00		10-03-2023 02:00
23		20	10-01-2023 20:00		10-03-2023 04:00
24		21	10-01-2023 21:00		10-03-2023 06:00
25		22	10-01-2023 22:00		10-03-2023 08:00
26		23	10-01-2023 23:00		10-03-2023 10:00
27		24	10-01-2023 00:00		10-02-2023 12:00
rockyhawkky

Cell Formulas
Range	Formula
D2	D2	=C2+(B2/24)
B4:B27	B4	=SEQUENCE(24)
C4:C27	C4	=DATE(2023,10,1)+TIME(B4,0,0)
E2:E27	E2	=IF(18/24-MOD(C2,1)<B2/24,12/24,0)+B2/24+C2
Dynamic array formulas.

this only works for 1 day jumps. so the ones that jump more than one day are incorrect.

 

[rockyhawkky]

@awoohaw @vladimiratanasiu much appreciate, i will try to understand how it's work.