How to create a time sensitive "Urgency Tracker" spreadsheet

In this post, I'll show you the structure of a relatively simple column you can add to your spreadsheet that can automatically alert you as to how much time has passed since you logged the task to ensure that you are completing these tasks in a timely manner.

First choose 6 cells in a place that can be hidden that will be used to indicate the time frames at which you want your spreadsheet to update its indicator.

For this example, we will be creating a spreadsheet that will show items dated in the future as "FUTURE", items dated within the past week as "LOW", items dated greater than one week in the past but less than two weeks in the past "MODERATE", and items dated greater than 2 weeks in the past as "URGENT".

To do this, we will need a cell reflecting today's date, a cell reflecting the date 1 week ago, and a cell reflecting the date 2 weeks ago. To create these cells, I will use the =TODAY() function. Enter these formulas into 3 cells that you can hide to get your 3 dates:

            Today's Date: =TODAY()

            Date 1 week ago: =TODAY()-7

            Date 2 weeks ago: =TODAY()-14

This is the =TODAY() function. It generates today's date. Adding a "-" manipulates the date to a prior date. Putting "7" after the "-" will subtract 7 days from the date. Likewise, if you entered "=TODAY()+7", the cell would populate with the date one week in the future.

When you have entered these formulas into the cells, they should look like this:

For "Date 1 week ago" and "Date 2 weeks ago", the results will populate in a special type of date formatting. If you would like these cells to populate in a "XX/XX/XXXX" format, right click on the cell> click "Format Cells"> click "Date"> Click "OK".

After performing this action on both cells, your sheet will look like this:

Now create the headers for your log. Include the regular identifying data. Make sure to include a field for "Date" and another field for "Urgency Level". For my example, I'm going to create an inventory tracker. This inventory tracker tracks the date a product is received, how many of the product is received and if the product has been stocked on the shelves. If the item has not been stocked, the urgency indicator will increase over time. Here is what my headers look like:

Fill in some dummy data that you can use to test your logic after you have built it. Try to focus your data on the areas you want to test. For this, you want to make sure you have a date in the future today's date, a date between today and a week ago, the date a week ago, a date in-between a week ago and two weeks ago, the date 2 weeks ago, and a date more than 2 weeks ago. It is generally wise to duplicate each datapoint, so enter each date twice:

Now we want to start on the heart of our spreadsheet, the Urgency Level field. For this we are going to use nested IF functions.

First, you want to be alerted if product was received more than 2 weeks ago. To do this, enter the following logical argument into the D2 cell:

            =IF(A2<$K$6,"URGENT")

This logical argument essentially states "if the Date column in this row is before the date listed in cell K6, then tell me that this item is URGENT."

Since we want our formula to appraise the date on the same row, we simply enter "A2". Since we want to compare all rows to K6, we enter "$" characters to fix the cell location.

Now click on the bottom right hand corner of the D2 cell, on the little square, and drag down to row 17. This will cause D2 through D17 to autofill with the formula. Notice that on row 7, the formula says "=IF(A7<$K$6,"URGENT")". The A value changed because it was not fixed in place with "$" symbols.

Also, letters are "string variables" in Excel. Because of this, whenever you want a formula to read or report a "string variable", that variable must be in quotations. This is why "URGENT" is in quotations in the formula. Quotation marks are not required for numbers.

Here is what your sheet will look like:

Our spreadsheet is now telling us that the shipments sent in on 5/1/13 urgently need to be stocked!

The rest of the dated fields are reporting "FALSE", because their dates are not earlier than the date in cell K6, and we have not told the formula what to do if the logical argument is not met.

The rows we have not entered dates into are returning as "URGENT" because the formula reads "null", or blank (not to be confused with 0, null and 0 are two completely different variables) as a value less than the date in K6. Fix this with the following amendment to your formula:

            =IF(A2="","",IF(A2<$K$6,"URGENT"))

Make sure to remember to add another close parenthesis to the end of the equation since there are now 2 logical arguments that need to be closed.

The argument in red is stating "If the date on this row is blank, then leave the cell blank. If the date on this row is not blank, go on to the next test". In Excel formula's quotations with no characters between them ("") are used to say "null" or "blank". Use "" to identify blank cells or to instruct a cell to populate as blank.

Because of the addition, now cells D16 and D17 are blank because there is no date entered into A16 or A17. The formulas are still in those fields and waiting to populate results, but they will remain blank until those rows are used.

After you have autofilled your formulas from D2 to D17, this is what your spreadsheet will look like:

Now lets work on our "MODERATE" urgency level. This will populate for product that is dated between 1 and 2 weeks ago. To do this, we are going to use the exact same argument as before, but instead of citing cell K6, we are going to cite cell K4 as our point of reference. Beware: Make sure this argument is placed after the "URGENT" arguments in the formula. If "MODERATE" is placed before "URGENT", all of the rows will report back as "MODERATE".

=IF(A2="","",IF(A2<$K$6,"URGENT",IF(A2<$K$4,"MODERATE")))

Autofill down to row D17 and your spreadsheet should look like this.

We added another logical argument. Our addition essentially says, "If the date on this row is before the date in cell K4, label the row 'MODERATE'"

Now lets enter in the logical arguments for "LOW". For this, rows that are dated within the past week will be labeled as "LOW". Use the exact same logical argument you used for both the "URGENT" and "MODERATE", just change the reference cell to K2. Nest the next argument in as follows:

=IF(A2="","",IF(A2<$K$6,"URGENT",IF(A2<$K$4,"MODERATE",IF(A2<$K$2,"LOW"))))

Because we also want shipments received on today's date to be labeled as "LOW" urgency, also make the following addition:

=IF(A2="","",IF(A2<$K$6,"URGENT",IF(A2<$K$4,"MODERATE",IF(A2<$K$2,"LOW",IF(A2=$K$2,"LOW")))))

After entering into D2, autofill down to D17 again. Your spreadsheet should now look like this:

Now we want to handle situations where the date entered is in the future, as is the case for cells D14 and D15. Why? Because it's a nice finishing touch to make sure an ugly "FALSE" doesn't appear and you never know if you are going to schedule a receipt on a future date and will want to be able to keep track of that here.

To add in this functionality, modify your formula again as follows:

=IF(A2="","",IF(A2<$K$6,"URGENT",IF(A2<$K$4,"MODERATE",IF(A2<$K$2,"LOW",IF(A2=$K$2,"LOW",IF(A2>$K$2,"FUTURE"))))))

Enter this formula into D2 and autofill to D17 and your spreadsheet should look like this:

Now if you enter a date in the future, your spreadsheet will tell you "FUTURE".

And now we incorporate our final functionality into this formula. We want the spreadsheet to tell us when the item is marked as having been stocked. To do this, we are going to have to nest in one final =IF logical argument. When the "Item Stocked" column is empty, we want the spreadsheet to report us what Urgency Level the item is at. When there is an x, "done", or other notation in the "Item Stocked" field, we want our spreadsheet to label that row as "COMPLETED".

Since there is no efficient way to make the statement "If the cell is not blank, label COMPLETE, we will need to create an separate hidden indicator field in the spreadsheet.

To do this, label the header for column F, and enter the following formula into cell F2:

=IF(E2="",1,0)

This formula says "If E2 is blank, then report a 1, if E2 is not blank, report a 0". Using this new formula, our main formula will now be able to detect of the "Item Stocked" cell is blank without using the "" reference for null. Make sure to enter some data in the E column (ex: x, Done, Complete) to verify the formula is functioning as desired

After entering the formula and autofilling down to F17, your spreadsheet will look like this:

Now we can add our final logical statement into our main formula, make the following amendments to your formula located in D2:

=IF(A2="","",IF(F2=0,"COMPLETE",IF(A2<$K$6,"URGENT",IF(A2<$K$4,"MODERATE",IF(A2<$K$2,"LOW",IF(A2=$K$2,"LOW",IF(A2>$K$2,"FUTURE")))))))

Autofill the formula down to D17 and your spreadsheet will look like this:

Your spreadsheet is now fully functional. All that is left to do is some light cleaning. Right click on the E column and select "Hide". Do the same for the "K" column and here is your final product:

I also, as a part of general practice, do a little dressing up. I always shade columns that should not be changed because they are automated functions gray. Change some font colors. Add some boarders... you know:

You are now the proud creator of an inventory tracking sheet that automatically monitors the urgency of tasks for you. As time elapses, hidden sells will automatically update, and they will cause your urgency levels to automatically upgrade. Likewise, if you complete the task, just enter a note indicating so and the urgency level move to "COMPLETE".

So in review, here are the steps broken down:

-Create points of reference for the time-frames at which you want your tracker to update.

-Build a nested =IF formula using logical arguments to sort out which rows go into which categories.

-Create a hidden column that can report on the status of the "action completed" column.

-Make a reference to your "action completed" column in your nested =IF formula to trigger a "COMPLETE" status.

And the side by side display of how the formula was built up:

=IF(A2<$K$6,"URGENT")

=IF(A2="","",IF(A2<$K$6,"URGENT"))

=IF(A2="","",IF(A2<$K$6,"URGENT",IF(A2<$K$4,"MODERATE")))

=IF(A2="","",IF(A2<$K$6,"URGENT",IF(A2<$K$4,"MODERATE",IF(A2<$K$2,"LOW"))))

=IF(A2="","",IF(A2<$K$6,"URGENT",IF(A2<$K$4,"MODERATE",IF(A2<$K$2,"LOW",IF(A2=$K$2,"LOW"))))

=IF(A2="","",IF(A2<$K$6,"URGENT",IF(A2<$K$4,"MODERATE",IF(A2<$K$2,"LOW",IF(A2=$K$2,"LOW",IF(A2>$K$2,"FUTURE"))))))

=IF(A2="","",IF(F2=0,"COMPLETE",IF(A2<$K$6,"URGENT",IF(A2<$K$4,"MODERATE",IF(A2<$K$2,"LOW",IF(A2=$K$2,"LOW",IF(A2>$K$2,"FUTURE")))))))

This formula depends on the secondary formula:

=IF(E2="",1,0)