How to setup custom expiry dates for tasks with visual reminders

Question

How to setup custom expiry dates for tasks with visual reminders

Elvis Necovski 20

Hi, I am creating a custom show management tool using MS Excel.

I need help with setting up expiry dates.

The expiry dates will work backwards from the show start date (B3) and change colour. Colour changes will include no change when still ok, orange when getting close to expiry and red when expired.

The show start date is B3

Example, The "Venue pick" task must be complete 50 days before the show starts.

Therefore, I put in the date 6th September 2024 in cell E8.

I want the text colour of E8 to change red when the date changes to 7th September 2024 meaning the task date has expired (49 days to show start date B3).

I want the text colour of E8 to change orange when the date is 64-50 days before the show starts.

I want the text to stay black/automatic for all dates before the 64 days before the show starts.

This will be the same formula but different expiry dates for the other tasks (E8-E13).

Just to note, there are over 100 tasks in total for the show and that list will possibly grow over time. I have only listed the first few (E8-E13)

Each task will have a custom expiry date.

Example, "Floor plan Venue final" needs to be complete only 14 days before the show starts.

The visual changes of text colour are an important feature.

I dont want to use a table off to the right of the spreadsheet or another sheet for just formulas.

I want everything calculated in the same worksheet.

I will also be duplicating the spreadsheet so i cant create the 2nd, 3rd, 4th show etc. so the formula/method has to work again and again as i duplicate worksheets.

I have tried everything including google searches, trying multiple formulas and ideas, even using AI but havent been able to work out a formula and method to achieve the required outcome.

I hope I have explained everything clearly enough. If you have any further questions please ask.

Accepted answer

0 additional answers

Your answer

Answer 1

Barry Schwarz 3,746

Since the 'venue pick" task must be completed 50 days before the start, B3 should contain 26 October 2024. Rather than hard code 6 September 2024 in E8, you should use the formula =B3-50. This will adjust automatically if the start date in B3 should change.

Similarly, if row 9 contains the "floor plan venue final" task , then E9 should contain the formula =B3-14.

Conditional formatting will allow you to change many visual aspects of a cell depending on how close the current date is to the desired completion date. In addition to the font color, you can change the font size, the font itself, the fill color, and the fill pattern. The choice is yours.

You will need to create a series of conditional formatting rules. The order of the rules is important. They must be in ascending order of time from the completion date. Each rule must set the Stop If True checkbox. For sake of example, assume row 8 contains the first task and row 63 contain the last. All the rules would apply to cells E8:E63

The "shortest" check you specified is "Has the completion date been passed?" The formula for this rule is =TODAY()>E8 and the format would set the font color to red.

The next check you specified is with in 14 days of completion. (You example for E8 showed this as within 64 of show start but that includes the 50 built into the completion date.) The formula for this rule is =(E8-TODAY())<=14 and the format would set the font color to orange.

You are apparently unconcerned in there are more than two weeks till the completion date.

You have not told us how you mark a task completed. Surely you don't want it in red or orange? And you probably don't want to leave it as back font in white cell because that is indistinguishable from a task still being worked with more than two weeks to go. For example, if column K is blank until filled in with the actual completion date, a new first formula applied to E8:E63 could be =NOT(ISBLANK(K8)) and the format would set the fill color to green.

Elvis Necovski 20 Reputation points

2024-10-13T09:32:05.58+00:00

Hi, Thanks for the reply.

Sorry, I missed the "Add image" button on the original post. I did select an image but didnt add it. I will add it now.

To answer your question, to mark a task as complete, Column C is the "Task Status" section and has a drop down box with 2 options, Not Complete and Complete. Im using conditional formatting rules to change the colour and font to bold. See attached snips. The person responsible for that task will change the "Task Status" when they complete the task.

At this point, I figure that the Due date has expired and so it can stay red. No need to change it.

I have created and applied your method and formulas. I believe they have worked.

You have provided a solution that I understand it and its logical.

The only downside is I will need to create 3 conditional formatting rules for each task.

But to be honest, if it works then its worth the effort because you only do it once.Thinking ahead, if the expiry date changes for a task in the future then I will have to manually change the numbers in the cell and conditional formatting rules as required but that cannot be helped and its a small price to pay for the benefit this management tool will provide overall.

If you have anything to add, please do so, otherwise thank you so much.
Barry Schwarz 3,746 Reputation points

2024-10-13T22:44:53.3+00:00

I don't know why you have rules (3 & 6)that check C3. Your picture shows C3 empty and your description said the show date was in B3. The only date that should affect how you format a task is the task completion date.

You are using colors to indicate how much more time you have to complete a task. You have specified red to indicate the task is past its completion date. This is only significant if it is not yet complete. Leaving a task red after it is completed has the undesirable effect of hiding incomplete tasks in weeds with all the complete tasks. Red should serve to indicate that this task is now urgent. But it is your document.

You don't need to create rules for each task. One set of rules covering all the task rows in column E will do the job with just 2 rules total. Delete rules 1, 2, and 3. (Rule 2 was incorrect anyway; it changed the formatting in row 13 based on the contents of row 8.) Change the Applies to box of rules 4 and 5 to $E$8:$E$13 (or whatever the first and last rows really are). In addition to formatting row 8 as desired, this single set will accomplish what rules 1-3 were trying to do in row 13 also. And finally delete rule 6.

And you can still add new first rule that applies to $E$8:$E$13 with the formula =C8="COMPLETE" to prevent completed asks from showing up red.
Elvis Necovski 20 Reputation points

2024-10-13T23:05:41.02+00:00

Thanks for picking up on that C3 issue in the rules. It should be B3.

I agree with your comment on the expiry date when it changes to red.

Maybe I can create a rule that says if the task is set to complete in the task staus section then the expiry date cell turns green or something like that to indicate it is complete even though it is expired.

Thanks for the additional input. I will have a play with it today.
Jiajing Hua 18,060 Reputation points Moderator

2024-10-15T01:07:40.2166667+00:00

Hi @Elvis Necovski

Have you tried Barry Schwarz's suggestion?

If it is helpful to you, please remember to accept Barry Schwarz's reply as an answer. Thx~

Share via

How to setup custom expiry dates for tasks with visual reminders

0 additional answers

Your answer