Need Help Building Excel Formula Concerning Days Overdue vs. Expected (with complications)

[SkylarL]

[FONT=&quot]I am building a quality assurance spreadsheet to track timeliness of document submissions. In Column A, I will list the date a document is expected to be issued. In Column B I will list either a date that the document was actually issued or a blank cell, representing a still outstanding document. Column C will list the difference in days between the expected issue date and the actual issue date, in cases when a document was issued, and, the difference in the expected issue date and today's date, in cases when document issuance is still outstanding. [/FONT]
[FONT=&quot]Additionally, Column C will display the color green when documents are issued in fewer than 15 days overdue or ahead of schedule, yellow when issued between 14 days overdue and 32 days overdue, and red when issued more than 31 days overdue or still outstanding (but only when an expected issue date is entered in Column A).[/FONT]
[FONT=&quot]Any help or advice on constructing a formula or series of formulas that meet these requirements would be greatly appreciated. If, in addition to crafting a formula, the respondent could explain the logic behind the formula... it would be especially helpful.[/FONT]
[FONT=&quot]Thank you.[/FONT]

 

[JoeMo]

This formula should get you started. Then you need to remove the ambiguities from your color fill ranges and use conditional formatting to produce the color fill. As an example of an ambiguous specification, note that 14 days overdue is less than 15 days overdue. Your first spec says if fewer than 15 the fill is green, but then you specify that anything in the range 14-32 days overdue should be filled yellow. So, is 14 days green or is it yellow?

Excel Workbook
	A	B	C
1	Expected	Actual	Diff
2	12/9/2017		-35
3	1/7/2018	1/12/2018	-5
4	12/6/2017	1/7/2018	-32
5	12/6/2017	12/21/2017	-15
6	12/7/2017	12/21/2017	-14
Sheet1

 

[SkylarL]

Thank you so much for the help, Joe. I will attempt to work with the formula you provided. As for the ambiguity conversation. The range for days was intentional, in an effort to eliminate less-than-or-equal-to statements. For my purposes... green<15 as two weeks overdue is acceptable, 14<yellow<32 (meaning any value 15 days overdue through 31 is concerning), and red for any entry greater than 31 days (exceeding the maximum number of days in a month for which a document has the potential to be overdue is unacceptable). Just some thoughts. If you can make sense of that.