Gantt Chart showing tasks in 1/4days using conditional formatting

[HighlandRanger]

Using WORKDAY.INTL to be able to hide weekends and holidays, is it possible to display upto 2 months and use conditional formating to show a gantt chart with days divided into 1/4s? I have presented a table with one week to show an example.

					22				23				24				25				26
FEATURE	BACKLOG ITEM	Effort	Start Date	End Date	M				T				W				T				F
Parent Task		4.75	2/22/21	2/26/21
	Child Task 1	1	2/22/21	2/22/21
	Child Task 2	0.25	2/23/21	2/23/21
	Child Task 3	0.5	2/23/21	2/23/21
	Child Task 4	1	2/23/21	2/24/21
	Child Task 5	0.5	2/24/21	2/25/21
	Child Task 6	0.5	2/25/21	2/25/21
	Child Task 7	1	2/25/21	2/26/21

 

[julhs]

Use "X12bb Mini sheet" and every one can see the formulas of the cells.
I for one have no idea what you are referring too with WORKDAY.INTL or gantt chart

 

[HighlandRanger]

Use "X12bb Mini sheet" and every one can see the formulas of the cells.
I for one have no idea what you are referring too with WORKDAY.INTL or gantt chart

The above example is a manually created table with no formula. I created it to show what I want to be able to do.

Here is a link to the WORKDAY.INTL function

WORKDAY.INTL function - Microsoft Support

This article describes the formula syntax and usage of the WORKDAY.INTL function, which returns the serial number of the date before or after a specified number of workdays with custom weekend parameters.

support.microsoft.com

 

[julhs]

My apologies, I believe I have under estimated my ability to solve your issue.
If no-one else looks in and picks it up, "Bump" the question in an hour or so

 

[JamesCanale]

Very simple Gantt with sequential logic.

MrExcelPlayground.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
3					2/1/2021				2/2/2021				2/3/2021				2/4/2021				2/5/2021				2/8/2021				2/9/2021
4	Task	Start	End	Duration	1	1	1	1	2	2	2	2	3	3	3	3	4	4	4	4	5	5	5	5	8	8	8	8	9	9	9	9
5	Overall	2/1/2021	2/9/2021
6	Task 1	2/1/2021	2/2/2021	1.25
7	Task 2	2/2/2021	2/4/2021	2.75
8	Task 3	2/5/2021	2/8/2021	1.75
9	Task 4	2/8/2021	2/9/2021	0.5
Sheet11 (2)

Cell Formulas
Range	Formula
F3	F3	=C6
J3,N3,R3,V3,Z3,AD3	J3	=WORKDAY.INTL(F3,1,1)
F4,J4,N4,R4,V4,Z4,AD4	F4	=F3
G4,K4,O4,S4,W4,AA4,AE4	G4	=F3+0.25
H4,L4,P4,T4,X4,AB4,AF4	H4	=F3+0.5
I4,M4,Q4,U4,Y4,AC4,AG4	I4	=F3+0.75
F5:AG9	F5	=IF(AND($C5<=F$4,$D5>=F$4)," ","")
C5	C5	=MIN(C6:C9)
D5	D5	=MAX(D6:D9)
D6:D9	D6	=IF(OR(WEEKDAY(C6+E6-0.25)=7,WEEKDAY(C6+E6-0.25)=1),C6+E6-0.25+2,C6+E6-0.25)
C7:C9	C7	=IF(OR(WEEKDAY(D6+0.25)=7,WEEKDAY(D6+0.25)=1),D6+0.25+2,D6+0.25)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AD5:AG9	Cell Value	=" "	text	NO
F5:AC9	Cell Value	=" "	text	NO

Workday.intl doesn't do fractions of a day - which makes the schedule logic tough to do. So it's hard coded with no sat or sun, and holidays are a problem.

 

[HighlandRanger]

Very simple Gantt with sequential logic.

MrExcelPlayground.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
3					2/1/2021				2/2/2021				2/3/2021				2/4/2021				2/5/2021				2/8/2021				2/9/2021
4	Task	Start	End	Duration	1	1	1	1	2	2	2	2	3	3	3	3	4	4	4	4	5	5	5	5	8	8	8	8	9	9	9	9
5	Overall	2/1/2021	2/9/2021
6	Task 1	2/1/2021	2/2/2021	1.25
7	Task 2	2/2/2021	2/4/2021	2.75
8	Task 3	2/5/2021	2/8/2021	1.75
9	Task 4	2/8/2021	2/9/2021	0.5
Sheet11 (2)

Cell Formulas
Range	Formula
F3	F3	=C6
J3,N3,R3,V3,Z3,AD3	J3	=WORKDAY.INTL(F3,1,1)
F4,J4,N4,R4,V4,Z4,AD4	F4	=F3
G4,K4,O4,S4,W4,AA4,AE4	G4	=F3+0.25
H4,L4,P4,T4,X4,AB4,AF4	H4	=F3+0.5
I4,M4,Q4,U4,Y4,AC4,AG4	I4	=F3+0.75
F5:AG9	F5	=IF(AND($C5<=F$4,$D5>=F$4)," ","")
C5	C5	=MIN(C6:C9)
D5	D5	=MAX(D6:D9)
D6:D9	D6	=IF(OR(WEEKDAY(C6+E6-0.25)=7,WEEKDAY(C6+E6-0.25)=1),C6+E6-0.25+2,C6+E6-0.25)
C7:C9	C7	=IF(OR(WEEKDAY(D6+0.25)=7,WEEKDAY(D6+0.25)=1),D6+0.25+2,D6+0.25)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AD5:AG9	Cell Value	=" "	text	NO
F5:AC9	Cell Value	=" "	text	NO

Workday.intl doesn't do fractions of a day - which makes the schedule logic tough to do. So it's hard coded with no sat or sun, and holidays are a problem.

Well, that works. But I would really like to be able to skip holidays and show employee time off too.

Something I could enter in a table. Like

EMPLOYEE	START	END	TOTAL DAYS
Ken	3/8/21	3/10/21	3
Derick	3/18/21	3/19/21	2
Simeon	3/18/21	3/22/21	5
Justin	3/29/21	3/31/21	3

Hmm... if only Workday.intl would do fractions. Any other ideas for a workaround?

 

[JamesCanale]

I spend a while trying to get it working - and it always had some kind of weirdness. I couldn't get it to work.

I custom VBA function would have to come into play here.

 

[Sharid]

Not sure if you have seen this or, if it will help Gantt Chart Download

 

[HighlandRanger]

Not sure if you have seen this or, if it will help Gantt Chart Download

Thanks, but that doesn't do .25, .5, .75 of a day.

 

[Sharid]

See this thread, its on a hourly gantt time chart, might give you some idea, as your are doing days in portions Mr Excel