Previous Topic Next Topic

Colour column based on dates

Closed
Angga06
Posts
1
Registration date
Sunday January 6, 2013
Status
Member
Last seen
January 6, 2013
 - Jan 6, 2013 at 01:39 PM
 Angga - Jan 17, 2013 at 08:19 AM
Hello,

I'm having a hard time to figure out a formula that colour the column with respect to dates.
Can anyone help me out on this please..here's the sample data below.

Where: F9:TT9 is the dates column where start date is 01/Jan/2012=F9 (only weekdays)

A11= Unit - LG01
B11= Meeting Date - 01/Feb/2012, i want to highlights (gray colour) the cell column where 1/Feb/2012 is..which is AE11,
C11= Concept Date

then from AE11 add 21days and highlights (pink) which is from AF11:AZ11, but if C11 is lesser than 21days / or equals to C11=dates ,

then add 22days and highlights (blue) from C11,


then from AE11 add 90days and highlights (yellow)



Thanks & Best Regards,


Jona

2 replies

Answer 1 / 2
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Jan 6, 2013 at 09:10 PM
add dates ? Not sure what you are asking for
may be a sample file would allow you to explain better and others to understand. Since this forum does not have functionality that would allow you to upload a sample file, you can post a sample file at some public file share site and post back the link to the file itself back here
0
Answer 2 / 2
Angga
Jan 13, 2013 at 05:56 AM
Hi rizvisa1,


Thanks for the quick response, please see link below for the sample data. Please try both link..thanks



http://we.tl/7WtypUu8Rc

https://www.wetransfer.com/downloads/e00d567cb48b6509328a8b886e8a456820130113104903/2c88f7


Looking forward for your help..


Thanks & Best Regards,

Jona
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Jan 13, 2013 at 07:58 AM
Your file never opens for me.
0
angga
Jan 15, 2013 at 04:31 AM
Hi Dear,

I'm using Excel version 10..please I really need your help..I will try to upload the pdf format. Please have a look maybe you will get my point.

Sorry I'm really not good in explaining things.

please see link below for pdf format.

http://we.tl/Bv85ffnM3v


Thanks,

Jona
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 769
Jan 15, 2013 at 12:20 PM
You sheet and your questions are not matching up. Could you use the sheet as an example to explain what you are seeking
0
Angga
Jan 17, 2013 at 08:19 AM
Hi,

Actually what I'm looking at is how to create a formula to update the Actual Status row chart.

In the sample data " each unit has two row chart" where the first one is the Actual Status / and the second is the milestone;

Chart Color code:

Gray = Briefing meeting stage - 3days
Pink = Concept Stage - 21days
Blue = Technical Stage - 22days
Tan = ADM Permit - 14days
Yellow = On Site - 60/ 90/ 120/ 150/ 180/ 190/ 200 /300 days

Example for below unit.

LG-10 E-MAX ...if they done the Briefing Meeting stage " 27/Dec/12 cell column" should turn to color Gray, and then from 28/Dec/12 to 17/JAN/13 (21days) turn to color Pink (concept stage), and then from 18/JAN/13 to 08/Feb/13 (22days) turn to color Blue ( technical stage) continue to next process plus (14days) turn to color Tan, plus (fit out days) turn to color Yellow.

But if todays date which is the "red line" lapsed the 17/JAN/13 and they are still in concept stage row chart should be push forward so it will be 17/JAN/13+21days+22days+14days+fit out days.

But if in the middle of 28/Dec/12 to 17/JAN/13 they 're now on Technical stage let say 5/JAN/13 the row chart should move backward Blue (technical stage) will start on 5/JAN/13 and continue the rest of the stages.


Hope I don't give too much pressure on you guyz :-..but really appreciate your help on this..


Thanks,

Jona
0