Log In
Register
Home
Active Member
March 7, 2024
Offline
Hi,
I have created an Excel Project Management Dashboard learning from the video on it from Ms. Mynda Treacy. Thank you for the excellent video. It is very resourceful. I am trying to use the Not Started, In Progress and Completed gantt formatting using Conditional Formatting. However all three formulas provided in the video and the website are giving the - The formula is missing an opening or closing parenthesis - error. Please advice how this can be corrected. Thank you.
Formula I tried pasting for - Not Started
=LET(CurrentP,K$8,Start,$F9,End,$G9,DurComp,$I9,Prog,$J9,DateComplete,WORKDAY.INTL(Start,DurComp,1)-1,DaysFormat,AND(Prog = 0, CurrentP >= DateComplete, CurrentP <= End),WeeksFormat,OR(AND(Prog = 0, CurrentP >= Start, CurrentP <= End),AND(Prog = 0, CurrentP + 6 >= Start, CurrentP + 6 <= End)),MonthsFormat, AND(Prog = 0, EOMONTH(CurrentP, 0) >= Start, CurrentP <= End),CHOOSE(DisplayPeriod, DaysFormat,WeeksFormat, MonthsFormat))
Formula I tried pasting for - In Progress
=LET(CurrentP,K$8,Start,$F9,End,$G9,DurComp,$I9,Prog,$J9,DateComplete,WORKDAY.INTL(Start, Durcomp, 1 ) - 1 ,DaysFormat,AND(CurrentP >= DateComplete + 1 ,CurrentP <= End),WeeksFormat,OR(AND(CurrentP >= DateComplete + 1,CurrentP <= End),AND(CurrentP + 6 >= DateComplete + 1 ,CurrentP + 6 <= End)),MonthsFormat,AND(Prog <> 1, EOMONTH(CurrentP, 0) >= DateComplete + 1,CurrentP <= End),CHOOSE(DisplayPeriod, DaysFormat, WeeksFormat, MonthsFormat))
Formula I tried pasting for - Completed
=LET(CurrentP,K$8,Start,$F9,End,$G9,DurComp,$I9,Prog,$J9,DateComplete,WORKDAY.INTL(Start, Durcomp, 1 ) - 1 ,DaysFormat,AND(CurrentP >= StartCurrentP <= End),WeeksFormat,OR(AND(Prog > 0, Start >= CurrentP, Start <= CurrentP +6,DateComplete >= CurrentP + 6),AND(Prog > 0 ,CurrentP >= Start,CurrentP <= DateComplete),),MonthsFormat,OR(AND(Prog > 0, Start >= CurrentP, Start <= EOMONTH(CurrentP, 0),DateComplete >= EOMONTH(CurrentP, 0)),AND(Prog > 0, CurrentP >= Start,EOMONTH(CurrentP, 0) <= DateComplete),AND(Prog > 0, DateComplete >= CurrentP,DateComplete <= EOMONT(CurrentP, 0))),CHOOSE(DisplayPeriod, DaysFormat, WeeksFormat, MonthsFormat))
Moderators
January 31, 2022
Offline
I tested the first two formulas in the worksheet and didn't get the missing parenthesis error. Though, the 'Completed' formula has a few typo's in it. I marked them below in bold red.
=LET(CurrentP,K$8,Start,$F9,End,$G9,DurComp,$I9,Prog,$J9,DateComplete,WORKDAY.INTL(Start, Durcomp, 1 ) - 1 ,DaysFormat,AND(CurrentP >= Start, CurrentP <= End),WeeksFormat,OR(AND(Prog > 0, Start >= CurrentP, Start <= CurrentP +6,DateComplete >= CurrentP + 6),AND(Prog > 0 ,CurrentP >= Start,CurrentP <= DateComplete),),MonthsFormat,OR(AND(Prog > 0, Start >= CurrentP, Start <= EOMONTH(CurrentP, 0),DateComplete >= EOMONTH(CurrentP, 0)),AND(Prog > 0, CurrentP >= Start,EOMONTH(CurrentP, 0) <= DateComplete),AND(Prog > 0, DateComplete >= CurrentP,DateComplete <= EOMONTH(CurrentP, 0))),CHOOSE(DisplayPeriod, DaysFormat, WeeksFormat, MonthsFormat))
When I try to enter the formulas in Conditional Formatting rules on my Mac I get the 'parenthesis' error. On my PC, however, they work without error.
Active Member
March 7, 2024
Offline
Riny,
Thank you for the quick response. That makes sense. I am using Mac and I am unable to use any of those formulas. Is there any work around for using Conditional formatting formulas in Mac when formula runs longer?
I need help with Not started, In progress, Completed color coding like gantt and another conditional formatting formula to highlight the task cells when they are overdue. Please advise. Thank you.
Moderators
January 31, 2022
Offline
This is very odd. I've never come across the issue that a CF rule is accepted on the PC but not on the Mac. I can't find anything about size limits for CF rules only on a Mac. I downloaded Mynda's original workbook that works perfect on both platforms. But when I go into CF and choose a rule to edit. Then, change nothing and press OK, the 'parenthesis error' pops up and I can only cancel out of the dialogue window.
Don't know why.
Mynda, can you have a look at it please?
July 16, 2010
Offline
Wow, this is a new issue to me. Let me reach out to Microsoft to understand if there's a workaround.
Active Member
March 7, 2024
Offline
Thank you for looking into this.
I have attached the version of excel I am using in my Mac. What Riny told is true about the CF formula, the length of the Custom Formula rule box is very less. If we open Mynda's workbook and copy the formula too, it only copies partially. I did shorten the formula like given below and the system takes it without giving error. However, the representation is not coming correct in my case especially for the Completed tasks. I will try to figure this out. Please let me know once you hear from Microsoft on the formula box length. Many Thanks!
Not Started
=LET(CurrentP,K$8,Start,$F9,End,$G9,DurComp,$I9,Prog,$J9, WF,OR(AND(Prog=0,CurrentP>=Start,CurrentP<=End), AND(Prog=0,CurrentP+6>=Start,CurrentP+6<=End)), MF,AND(Prog=0,EOMONTH(CurrentP,0)>=Start,CurrentP<=End), CHOOSE(DisplayPeriod,WF,MF))
In Progress
=LET(Start,$F9,End,$G9,DurComp,$I9,Prog,$J9, DC,WORKDAY.INTL(Start,DurComp,1)-1, WF,OR(AND(K$8>=DC+1,K$8<=End), AND(K$8+6>=DC+1,K$8+6<=End)), MF,AND(Prog<>1,EOMONTH(K$8,0)>=DC+1, K$8<=End),CHOOSE(DisplayPeriod,WF,MF))
Completed
=LET(DurComp,$I9,Prog,$J9,DC,WORKDAY.INTL($F9,DurComp,1)-1, MF,OR(AND(Prog>0,$F9>=K$8,Start<=EOMONTH(K$8,0), DC>=EOMONTH(K$8,0)),AND(Prog>0,K$8>=$F9, EOMONTH(K$8,0)<=DC),AND(Prog>0,DC>=K$8, DC<=EOMONTH(K$8,0))),CHOOSE(DisplayPeriod,WF,MF))
July 16, 2010
Offline
Hi Chaitra,
It seems this is a character limitation in Mac Excel, so all you can do is try to shorten the formula. You could do this by moving parts of it to helper columns/rows in your workbook and then referencing the outcomes of those. You could also shorten some of the names in your formula from 'CurrentP' to 'CP' etc.
Mynda
Active Member
March 7, 2024
Offline
Hi Mynda,
I could not succeed using the dynamic periods and I resorted to just daily format for now. I need help with Conditional Formatting for adding Completed, In Progress and Not started bars. I used same formula's and order of the CFs per your video, but the format is not coming properly. Could you please advice? Thank you.
July 16, 2010
Offline
Hi Chaitra,
You have named the wrong column 'Durcomp'. It should be column I, not G. Also, you are missing your holiday dates from your WORKDAY.INTL formulas.
I didn't check all of your formulas, but found these two errors. See if that points you in the right direction.
If you're still stuck, come back with specific issues.
Mynda
1 Guest(s)
