Categorize Deadlines Based On Past Due/Period Remaining

[U_Shrestha]

Hi all,

Note: I have attached a new sample sheet named "CategorizeDeadlinesIntoMoths-Revised" in the following reply.

I have a table (Worksheet "ComplianceDates") that lists all the different types of Tests required for a list of stations, the last date performed and the next due date for the test to be performed.

I am looking for a formula that will categorize diffrent types of test required based on past due, 1 - 30 days left, 31-60 days left, 61 - 90 days left, along with the stations number/name.

Can someone please help me with this. My attachment would explain my problem more clearly. Thank you very much.

[Bob Phillips]

You say ... This cell should have the corresponding value in Row 7 from the sheet ComplianceDeadlines, e.g. Fire Supp. (See AB7 in the sheet ComplianceDeadlines

Why AB7, why Fire Supp?

ANd why have columns for 1-30, 31-60, of which only one will be data filled, why not have one column stating if it is 1-30,31-60, etc.

[U_Shrestha]

Hi xld,

You say ... This cell should have the corresponding value in Row 7 from the sheet ComplianceDeadlines, e.g. Fire Supp. (See AB7 in the sheet ComplianceDeadlines

Why AB7, why Fire Supp?
I want to tell the customer what type of test are due; the values in row 7 are the Type of Test, so that custormer will easily know which test (e.g Fire Suppression or Tank Test etc.) are due easily.


ANd why have columns for 1-30, 31-60, of which only one will be data filled, why not have one column stating if it is 1-30,31-60, etc.
Because I want to send this page as an email message (not a Excel Worksheet) periodically to the clients; I thought this way client can have a better idea of how much deadline they have to perform a test.

If this is a not a good idea, in what other ways can I show the report to the clients? Thanks again.

I saw several example in our knowledge base that I think I can use to send the report automatically to the clients based on the new report.

[Bob Phillips]

Hi xld,

I want to tell the customer what type of test are due; the values in row 7 are the Type of Test, so that custormer will easily know which test (e.g Fire Suppression or Tank Test etc.) are due easily.

That might mean something to you, but I am afraid it has added nothing to my undesrtanding.


Because I want to send this page as an email message (not a Excel Worksheet) periodically to the clients; I thought this way client can have a better idea of how much deadline they have to perform a test.
If this is a not a good idea, in what other ways can I show the report to the clients? Thanks again.

I saw several example in our knowledge base that I think I can use to send the report automatically to the clients based on the new report.

Dosen't just one column do that, or even if you use multiple columns, just one column per time nband with an X or something to denote it is this time band.

[U_Shrestha]

Hi xld,

Would you please look at the new attachment? I have combined all the dates in one column. Now, is it possible to have this one column list all the dates in the following order:

a) past due (First list the most past due date, then the rest..)
b) due in 1-30 days (First list the nearest due date, then the rest..)
c) due in 31-60 days (First list the nearest due date, then the rest..)
c) due in 61-90 (First list the nearest due date, then the rest..)

As far as showing the type of test is concerned, can I just show the value from column heading on which the next test date lies (Please see attachment)?

Can you please help me with this? Thanks.

[U_Shrestha]

Any chance for me? :(

[Aussiebear]

Hi U_Shrestha, This is becoming quite a project you have in mind here. There are four distinct different requests being asked for here.

1. A function to copy data from the Compliance Date Sheet to the CD Report range A4:A32 but what triggers this function? Is it when a date becomes red flagged? Not only do you what it copied over but it apparently needs to be sorted into 3 sub groups 0-30, 31-60, 61-90.

2. A formula to insert corresponding data based on the value of the cell in the range A4:A32. You should be able to write an Index Match formula to do this based on =Index(Range to look in,Match(What to look for, Where to look, Type of match))

3. A similar formula to that of request 2, but having its trigger value in a cell based in the range B4:B32

4. A formula to classify the sub group type. This can be done simply by using the following =If(A4-$B$1>60,"Due in 61-90 days",If(A4-$B$1<30,"Due in 31-60 days","Due in 1 -30 days")). Place this in cell D4 and copy down

While you are waiting for someone to assist you with request 1, you might find the time to work with that which I've given you to assist with requests 2,3 & 4. OK?

[U_Shrestha]

Hi Aussiebear.

Thanks for the feedback. I will comment only on item 1 for now:


1. A function to copy data from the Compliance Date Sheet to the CD Report range A4:A32 but what triggers this function? Is it when a date becomes red flagged? Not only do you what it copied over but it apparently needs to be sorted into 3 sub groups 0-30, 31-60, 61-90.

The colors should trigger the function. In the ComplianceDate Sheet the due dates change using case statements into Red, Orange, Yellow And Green. As long as the due date are in descending order (past due dates first and then the nearest dates), I am ok with whether the dates are placed in one column or multiple columns. Thanks again.