• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Working with the AND function in combination with IF(AND()) in a Gantt chart.

J

jyanguela

New Member
I am currently trying to solve an error that occurs when I combine two formulas and haven't been very successful.


Both of them are used in a Gannt chart and work well individually, however, when I combine them it returns an error.


=AND(IF(AND(Q$23>=$O26;Q$23<=$O26+$N26);IF($O26+$N26*$HI$27>Q$23;$HI$26;"");"");WORKDAY(O26;N26-1;$HO$22:$HO$46))


Q$23 gantt chart calendar sucession.

O$26 Start date.

N$26 Duration.

$H$27 Percentage (always 100% )

$H$26 The in-cell square to track completed time ( great idea by the way)

$HO$22:$HO$46 List of holidays in my location.


I have been unable to write the propper AND formula to combine the workday display with the in cell squares for the actual duration.


Any help is much appreciated.


Thank you Chandoo!!!
 
I think you want the CONCATENATE function instead of AND:


=CONCATENATE(IF(AND(Q$23>=$O26;Q$23<=$O26+$N26);IF($O26+$N26*$HI$27>Q$23;$HI$26;"");"");WORKDAY(O26;N26-1;$HO$22:$HO$46))
 
Thank you TessaES but it doesn't work. the value it returns doesn´t allow for the weekend days to be excluded and the cells give back an unwanted numerical value.


Thank you for taking an interest.
 
@Jyanguela

Shouldn't your formula use "," instead of ";"


Are you trying to do this

=IF(AND(Q$23>=$O26,Q$23<=$O26+$N26),IF($O26+$N26*$HI$27>Q$23,$HI$26,""),"")&WORKDAY(O26,N26-1,$HO$22:$HO$46)
 
I should,however when I use "," instead of ";" it comes back with the message "The formula you typed contains an Error" and persists until I write it with ";".


BTW I tried it this ways you wrote it(Without the "AND" Fucntion and using "&" instead) and it comes back with a numeric cell value in the workdays and with a "#VALUE!" error on some workday cells within the duration od the task and also on the weekends.


Thanks HUI.
 
What country version of Excel are you using ?


Did you try

=IF(AND(Q$23>=$O26;Q$23<=$O26+$N26);IF($O26+$N26*$HI$27>Q$23;$HI$26;"");"")&WORKDAY(O26;N26-1;$HO$22:$HO$46)
 
I think I know understand what you are trying to do: the duration is in workdays.

Try this:


=IF(AND(Q$23>=$O26;Q$23<=WORKDAY(O26;N26-1;$HO$22:$HO$46));IF(WORKDAY(O26;N26-1;$HO$22:$HO$46)*$HI$27>Q$23;$HI$26;"");"")


It will still plot a character for the weekends/holidays, but the total length should be ok.


If the percentage in HI27 is always 100% you can simplify the formula to:


=IF(AND(Q$23>=$O26;Q$23<=WORKDAY(O26;N26-1;$HO$22:$HO$46));$HI$26;"")
 
Hey guys,


Thank you for your answers.

HUI, my office 2010 program is in english but at the office we use spanish. I tried using your modification to the formula and what comes back is very peculiar. the first two cells in the in the duration display the in-cell square and a 5-digit number but the rest of the cell in the length display a #value! Error, weekend cells included


TessaES, the same happens when I try you suggestion, the first two cell are fine, to the point of displaying only the in-cell square, but after the third cell it returs the #value! Error i've been getting.


Thank you both so much.
 
Have a look here for sites you can post to:

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Dear HUI,


The file has been uploaded here:


http://rapidshare.com/files/414859059/Checklist_Proceso_RCPs.xls

MD5: 4680B2F113C41D71DAD55451C53F8893
 
I have just found a page that explains a bit more on the subject.

It says that to combine two formulas you have to put the + sign between them and presscntrl+shift+enter. Curly brackets ({) will automatically appear around the whole formula. It is something that they call AFs (Array-Entered Formulas).


I tried it and it seemed to work when I combined them:


=IF(AND(Q$23>=$O26;Q$23<=$P26);$D$12;"")to fill in the in-cell squares on the time length the activity took. ($D$12 is where I inserted the square symbol from symbol button on the insert ribbon)


And


=WORKDAY(O26;N26-1;$HO$22:$HO$46) to have the in-cell squares excluded from the weekends and holidays.


It looks like this after excel modified it:


=IF(AND(Q$23>=$O26;Q$23<=$P26);$D$12;"")&WORKDAY(O26;N26-1;$HO$22:$HO$46)


When I run the show calculation steps in the error correction on the cell it displays the error occuring after $D$12 (The inserted square symbol)


It shows this:


The next evaluation will result in an error: ".";&WORKDAY(O26;N26-1;$HO$22:$HO$46)


At first I tried it with the plus sign and turned back the #VALUE! error it always results in but when I tried "&" instead it also returned the error...


This is so frustrating...
 
Jyanguela


If I have read this right, you are trying to add a date to some text from D12 depending on some formulas?

You can't just append a date to a Text as they are incompatible

So to convert the date to a Text use the Text function like


=IF(AND(Q$23>=$O26,Q$23<=$P26),$D$12,"")&TEXT(WORKDAY(O26,N26-1,$HO$22:$HO$46),"dd mmm yy")


or


=Concatenate(IF(AND(Q$23>=$O26,Q$23<=$P26),$D$12,""), TEXT(WORKDAY(O26,N26-1,$HO$22:$HO$46),"dd mmm yy"))


One proviso is that the character in D12 must be in the same font as where your formula is otherwise it may not display correctly
 
Hello Hui,


What I am trying to do is to have a cell range in a gantt chart be filled from one cell to another (Start date to end date)with a symbol; the same way that a conditional formating would fill a cell with color if condition is met.


You can see on the file I sent you that it is a Gantt chart checklist that has planned Start/end/duration and Excecution Start/End/Duration.


The planned Columns(K=Duration, L=Start, M=End) are linked to a conditional formating on the range $Q26:$HH26 (First task is on row 26)that are as follows:


To highlight the duration of the activity. =AND(Q$23>=$L26;Q$23<=$M26)


To highlight the weekends and holidays with a different color.

=WORKDAY(L26;K26-1;$HO$22:$HO$46)this formula is inserted on the end date of

the planned columns.


The thing that I am trying to do is to insert a formula on the range $Q26:$HH26 that wil behave as the conditional formating described for the planned columns but that inserts symbol on the duration instead of having it highlighted , thus allowing you to see both the highlighted "planned" duration and the "Actual" excecuted duration at the same time, without highlighting again.


Everything works perfectly if I use only: =IF(AND(Q$23>=$O26;Q$23<=$P26);$D$12;"")wich inserts one symbol(Located on $D$12) per cell throughout the whole duration.


The problem occurs when I want to have it skip the weekends and holidays using:

=WORKDAY(L26;K26-1;$HO$22:$HO$46).


This has been the thorn on my side for weeks now.


I tried the formulas you suggested in your latest post and once again, the first two cells ($Q26 and $R26) are perfect, no error message appears and the symbol is displayed as wanted. After these first two, however, the Message #VALUE! appears in the cell, and the symbol I expected inserted is not there...


I don't know If I have thanked enough Hui, but just in case,


Thank you for your invaluable help.
 
Jyanguela

Put your symbol in D12

and Give this a go in Q26 and copy across and down


=IF(OR(WEEKDAY(Q$23,2)>5,IFERROR(MATCH(Q$23,$HO$22:$HO$46,0),0)>0),Q$23,$D$12)


You can change the Final Q$23 to be what ever you want if it isn't a weekend or holiday
 
Hello Hui,


The error is gone, leaving only the wanted symbol in the cell and the weekends and holidays are filled with a five digit number.

The number I have removed with conditionally formating the font to be the same color as the cell; However, all the cells have been filled in with the symbol!

It doesn't perform as dictated by the actual Start/end/duration columns and I can therefore, not know whether it's working according to what i want of it (That the duration in days is filled on the gantt chart dates, skipping weekends and holidays).


I like the fact that the damn error is gone, so I know that partially we´ve made some progress here.


I'll keep trying.


Thanks
 
I figured as much. There isn't suppossed to appear any value in the weekends or holiday, the formula is suppossed to skipped them.


So if a task has a duration of five days and begins on a thursday the in-cell symbol located on $D$12 would appear on the cells of thursday, friday, monday, tuesday and Wednesday. thus skipping the weekend; and if monday would happen to be a holiday the task would end on Thursday. (a symbol would appear on each cell of those days).
 
You must log in or register to reply here.
Back
Top