I have a problem with timesheet that I'm working on. Actual days are not moving as they should based on starting date using conditional formatting. I suppose that problem is in formula so if someone could check the formulas and let me know how to fix it I would appreciate it. I've attached and spreadsheet.
Screenshot 2024-01-30 081235.png
Two named ranges added:
Cal.Sched ='CH GM Mobilisation Plan'!$H$6:INDEX('CH GM Mobilisation Plan'!$6:$6,LastSchedDate)
LastSchedDate =MATCH(9E+303,'CH GM Mobilisation Plan'!$6:$6)
Conditional Format formulas:
Actual days (orange/stripe):
%comp (green)
Please Login or Register to view this content.
MOB DAY (YELLOW):Please Login or Register to view this content.
Column B, C, D use random numbers for testing Press F9....Please Login or Register to view this content.
Ben Van Johnson
Dear Ben
Thank you so much for contribution, I've tested using random and it's working but when I try to add manual date on column "start date" nothing changes.
Could you please checkScreenshot 2024-01-31 083615.png
Easier if you attach the workbook. NOT a picture of it. It may be the way that you have entered the date.
It works fine here.
AliGW on MS365 Beta Channel (Windows 11) 64 bit
MOBILISATION Notification of award
Sheet: CH GM Mobilisation Plan
Last edited by AliGW; 01-31-2024 at 03:44 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Dear Ali
Here is workbook.GCG Clarion GM Mobilisation (BVJ).xlsx
You need to enter dates with slashes, not dots (e.g. 11/14/23. NOT 11.14.23).
See the top row in the attachment.
Last edited by AliGW; 01-31-2024 at 04:02 AM.
I've tried and it that way and it's not working.
Yws it is - see the attachment to post #6.
You should be entering dates as dd/mm/yy for your locale, though: 14/11/23, NOT 11/14/23, which is US format mm/dd/yy).
AliGW yes you are right, now it's working thank you so much for your contribution.
You might want to change the way that you are formatting dates - change it to SHORT DATE instead of CUSTOM.
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Dear Ben
I've notice that if completion percentage is 0% there will be always one cell colored green. Is it possible change that if 0% of completion then no green colour on cell should be?
Try changing the green rule to:
=AND($F8>0,((INDEX(Cal.Sched,COLUMN()-7)=MEDIAN($B8,INDEX(Cal.Sched,COLUMN()-7),$B8+ROUNDDOWN($C8*$F8,0)))*3)=3)
Hmm it's not working only what happen that green colour disappear, but then when I try to add some percentage nothing happens.
Again, provide a workbook! It's working fine here - you have probably done something incorrectly again. Who knows witbout seeing the workbook???
AliGW on MS365 Beta Channel (Windows 11) 64 bit
Notification of award Carry out debriefing meeting with bidding team Issue Mobilisation plan
Sheet: CH GM Mobilisation Plan
Thank you again for response, it works now, reason why it was not working was because I added one more row so instead of F8 I had to use F9 row
OK - make sure you have it as $F9 in the rule, and not just F9.
In future, test proposed solutions BEFORE making other changes!!!
Sure thank you again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks