Gantt Chart (1 Viewer)

Jonny45wakey

New member
Local time
Today, 12:01
Joined
May 4, 2020
Messages
10
Hi all

Hoping someone could help with a little structuring of a formula in a text box i have which uses dates in other text boxes to populate a Gantt chart (image attached)

When dates are entered in the reference date text boxes the formula in each of the squares representing each week of the year will change colour using the following formula in the text box control source = =IIf(DateValue([Finish Date1])>=[Text1159]+7*0,IIf(DateValue([Start Date1]<=[Text1159]+6+7*0,"True","False"),"False")

The conditional formatting of the text box adds the blue colour shading if true condition is met, what i would like is to get rid of the Error messages you see below

#Name?

Thanks

Jonny

gantt.jpg
 

cheekybuddha

AWF VIP
Local time
Today, 12:01
Joined
Jul 21, 2014
Messages
556
Hi,

Perhaps test that you have dates for all you inputs first:
= =IIf(IsDate([Finish Date1]) AND IsDate([Start Date1]) AND IsDate([Text1159]), IIf(DateValue([Finish Date1])>=[Text1159]+7*0,IIf(DateValue([Start Date1]<=[Text1159]+6+7*0, True, False), False), False)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:01
Joined
Oct 29, 2018
Messages
11,105
Hi Jonny. Belated welcome to AWF!
 

Jonny45wakey

New member
Local time
Today, 12:01
Joined
May 4, 2020
Messages
10
Thanks cheekybuddha, i tried your suggestion but got the following:-

1591369152706.png
 

plog

Banishment Pending
Local time
Today, 06:01
Joined
May 11, 2011
Messages
9,928
Cheeky is missing a right parenthesis, but I think that's only going to fix the #Type errors not the #Name errors.

Are you sure [Text1159] is a valid reference? Compare the code In Row 10 Day 19 to Row 10 Day 20. Ones a #Type and the other is #Name. The answer is there.
 

cheekybuddha

AWF VIP
Local time
Today, 12:01
Joined
Jul 21, 2014
Messages
556
>> Cheeky is missing a right parenthesis <<
True, but copied from OP - apologies, didn't check my inputs! 😬
 

arnelgp

error reading drive A:
Local time
Today, 19:01
Joined
May 7, 2009
Messages
9,872
you can also use:

=Switch(DateValue(Nz([Finish Date1],0))>=[Text1159]+7*0 And DateValue(Nz([Start Date1],0)<=[Text1159]+6+7*0, "True", True, "False")
 

Jonny45wakey

New member
Local time
Today, 12:01
Joined
May 4, 2020
Messages
10
Hi all, thanks for the help but still cant sort the #Type! error!

The Textbox control source looks as follows:
1591622490817.png

Text 1159 refers to the following calculation in textbox1159

1591622544812.png

Essentially the #Type! error occurs when there are no dates entered into [Start Date6] and [Finish Date6] text boxes, what i need is something to leave textbox2337 blank if no dates in these text boxes?

1591622824751.png

Thanks in advance for further help

Jonny
 

Attachments

arnelgp

error reading drive A:
Local time
Today, 19:01
Joined
May 7, 2009
Messages
9,872
add a Public function to your form:


Code:
Public Function CellValue(byval StartDate As Variant, Byval EndDate As Variant) As Variant
StartDate=Trim(StartDate & "")
EndDate = Trim(EndDate & "")
If StartDate = "" Or EndDate = "" Then
    Exit Function
End If
StartDate = DateValue(CDate(StartDate))
EndDate = DateValue(CDate(EndDate))
CellValue=(EndDate >= ([Text1159]+7*0) And StartDate <= ([Text1159]+6+7*0))
End Function
on Each Unbound textbox:

=CellValue([Start Date1], [End Date1])
=CellValue([Start Date2], [End Date2])
=etc...
 

MickJav

AWF VIP
Local time
Today, 12:01
Joined
Nov 28, 2005
Messages
2,082
Hi Jonny45wakey

Correct me if I'm wrong but you are adding unbound controls a line at a time at least thats what it looks like in post 11 Your OP didn't show it.

if you are you will run out of controls at around 750

why all the mess with the Expressions you could very easly do what you need with a loop.

I've built a number of this type of object and would never use your approch.

mick
 

Jonny45wakey

New member
Local time
Today, 12:01
Joined
May 4, 2020
Messages
10
Hi Jonny45wakey

Correct me if I'm wrong but you are adding unbound controls a line at a time at least thats what it looks like in post 11 Your OP didn't show it.

if you are you will run out of controls at around 750

why all the mess with the Expressions you could very easly do what you need with a loop.

I've built a number of this type of object and would never use your approch.

mick

Hi MickJav

Are you able to share any of these objects so i can see if i can apply these to my project?

Thanks

Jonny
 

cheekybuddha

AWF VIP
Local time
Today, 12:01
Joined
Jul 21, 2014
Messages
556
Essentially the #Type! error occurs when there are no dates entered into [Start Date6] and [Finish Date6] text boxes, what i need is something to leave textbox2337 blank if no dates in these text boxes?
Did you try with the correction made to the expression I suggested in post#2 ?
Code:
=IIf(IsDate([Finish Date1]) AND IsDate([Start Date1]) AND IsDate([Text1159]), IIf(DateValue([Finish Date1])>=[Text1159]+7*0,IIf(DateValue([Start Date1])<=[Text1159]+6+7*0, True, False), False), False)
 

cheekybuddha

AWF VIP
Local time
Today, 12:01
Joined
Jul 21, 2014
Messages
556
Out of curiosity, what does this part of the expression mean?
DateValue([Start Date1])<=[Text1159]+6+7*0

Surely it's thsame as writing:
DateValue([Start Date1])<=[Text1159]+6

??
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom