Highlight Cells if between certain dates. (Timeline)

[barleyhill]

I have a list of 4000 Letters. Each letter has a revision 1 and revision 2 (so effectivly 8000 letters).


Each revision has a response due date and actual response date.


My query is: I would like to plot on a timeline showing in colour the delay period. (If the Actual response Date is > than the response due date.

I have an excel document showing an example of before and after if anyone would like to tackle my query.

Many thanks in advance.

 

[Akashwani]

Hi and welcome to MrExcel.

I'm sorry, but I doubt that I can help you.
You may want to either post a sample of your data and the expected results, or upload your Workbook to a free site and provide a link to it.
You can do either of these by using one of the links in my signature.

Good luck.

Ak

 

[barleyhill]

Hi Akashwani,

THank you. I have uploaded the document to one of the sites you mention. Hope I have done this correctly.

http://www.filedropper.com/questionbeforeandafter

<a href=http://www.filedropper.com/questionbeforeandafter><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >file backup online</a></div>

Hi and welcome to MrExcel.

I'm sorry, but I doubt that I can help you.
You may want to either post a sample of your data and the expected results, or upload your Workbook to a free site and provide a link to it.
You can do either of these by using one of the links in my signature.

Good luck.

Ak

 

[Akashwani]

Hi,

Based on this sample of your data....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1						Week Ending	24/07/2012	31/07/2012	07/08/2012	14/08/2012	21/08/2012	28/08/2012	04/09/2012	11/09/2012	18/09/2012	25/09/2012	02/10/2012	09/10/2012	16/10/2012	23/10/2012	30/10/2012	06/11/2012	13/11/2012	20/11/2012	27/11/2012	04/12/2012	11/12/2012	18/12/2012
2		Revision 1		Revision 2
3	Description	Letter Response Due Date	Actual Response Date	Letter Response Due Date	Actual Response Date
4	Letter 1	25/07/2012	06/08/2012	24/09/2012	01/10/2012			1	1							2	2
5	Letter 2	29/08/2012	26/09/2012	22/10/2012	06/11/2012								1	1	1	1	1			2	2	2
6	Letter 3	07/11/2012	06/11/2012	08/11/2012	26/11/2012																		2	2	2
7	Letter 4	19/12/2013
8
How Should look

It appears to me that you are wanting to produce a Gantt Chart. take a look here...

ExcelIsFun - YouTube

I hope that helps.

Ak

 

[barleyhill]

yes you are correct. Thanks..

Insert 1 in cell b1 and 2 in cell d2.. Then proceed to cell g4 for the formula...

In cell G4, so far I have.. =IF((AND(G$1>=$B4,G$1<=$C4)),$B$1)(AND(G$1>=$d4,G$1<=$e4)),$d$1),"")


but not working

 

[Akashwani]

Hi,

I'm not sure how to produce the formula when C4, D4 and E4 are blank.

Try this to see if it gets you a bit nearer to a solution....

=IF(AND($B4<=H$1,$C4>=G$1),1,IF(AND($D4<=H$1,$E4>=G$1),2,""))

Ak

 

[barleyhill]

Figured it out.. so in cell g4 i have

=IF(AND(G$1>=$B4,G$1<=$C4),1,IF(AND(G$1>=$D4,G$1<=$E4),2,0))

Thanks.

 

[Akashwani]

Hi,

I'm sorry that I couldn't have been of more help, but I'm pleased you have been able to solve it and thanks for the feedback.

Ak