Color Coded Countdown Dynamic Due Dates

Hello Great Minds of this Forum - I thank you in advance for helping me. I am currently using chromebook online excel to practice (limited), but when I start my new job in June, I will have a more traditional deskop version for a large hospital.

Here is my novice issue - I am a social worker with due dates based off the patient intake date and sometimes the last action date.

I need a spreadsheet that gradually reminds me of important dates coming up. For me, I like the color-coded method the best to signify specifically two weeks from due date (green), one week from due date (yellow) 2 days from due date (orange) and past due (red). Here is what I would like to have:

Along one row would be headers that signify the meaning of the due date. In chronological order this would be i.e. Admit Date, Psycho Social, Tx PLanning, TPR 1, TPR 2, TPR 3. NOW......heres the complicated part. Psycho Social and Tx Planning are due 30 days from admit date, but it has to be on a weekday rounding back to Friday if it falls on a weekend. TPR 1 is due 90 days from admit date same weekday rule and TPR 2 is due 90 days from TPR 1 with same weekday rule. The admit date obviously changes for each new patient and it would be amazing if it populated the rest of the row WHILE implementing my color code countdown WHILE giving me tha ability to click on a header and have it reorder the list so all my colors stack up together so I know which pateints I need to work on next according to urgency color. Does this make sense? Sorry for this big challenge, but if I dont have an easy way to know when this stuff is due, Ill surely get behind. Thank you SO MUCH!

Thank you - I would love to upload a sample - how do I do that?

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data and contain the result what you want to achieve (manually created if necessary)
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

Thank you so much Sandy - your quotes are awesome. I have to go to bed - been at this for hours trying to make sense of it all. Learning a lot. I will have to make the desired effect spreadsheet during the weekend and will upload following your gracious instructions. Thanks for the help so far. I thank you and my patients thank you!

You are welcome
but real solution for your issue is from Ford.

Wow Everyone - overwhelmed by all of you! Thank you for this help. This is all mindblowing as I am such a novice at excel.
John - amazing - the break out of days of the week is something to consider. To answer your question, my plan is to block off times during the week to complete certain impending tasks. The idea is that each column represents one of the blocks of time. Your observation of completed dates is a good one to which I have no answer. Is there a check off method in excel? I will build a moch version of what Im looking to do by the end of the weekend as I am very busy with family and work this weekend, so I hope to tackle Sunday night. For now, the most direct way I know how to explain what Im looking for is:

Have a formula I can repeatedly come back to that takes whatever date I enter in a cell (not necessarily today), and gives me color-coded warnings (in that same cell) as that date approaches. Optimally part of the formula would populate the future due dates across the row so all I would have to do is enter the admit date, but maybe thats too complicated. I don't mind doing that manually if I have to, but to have the due date change colors as it gets closer is the desired effect.
Within one month = black font, white background
Within 2 weeks = black font, green background
Within 1 week = black font, yellow background
Within 2 days = black font, orange background
Overdue = black font, red background

Thank you all for your patience - I will get a mock-up done by late Sunday night. Have a great weekend everyone.

I would even consider ditching the layout Ive been discussing to simply have what this person has brilliantly created. Unfortunately, I have NO IDEA what the formulas are but this would be awesome. Definately worth a watch. If I could have this I would be all set, however I see this forumn wont allow me to c&p a link so the youtube video is by Michael Dew from Sept. 1, 2013 called "Excel spreadsheet providing list of reminders / future tasks / tp-do items (Video 1 of 3)". It has over 170,000 views and its brilliant.

IN fact - the more I think about it, the more Mr. Dew's solution is my goal. I would like to precisely replicate what he's doing in the video.....but how???

Why not use his spreadsheet: a downloaded copy is attached.

Optimally part of the formula would populate the future due dates across the row so all I would have to do is enter the admit date,

The file i provided does calculate the 30/90 day periods, taking into account that days can only be Mon_Fri.

I don't know if that can be easily incorporated into the Michael Drew spreadsheet.

OMG JOhn I had no idea he made it available to us! Problem solved! Thank you so much!

Unfortunately excel online on the Chromebook is not robust enough. Bunch of elements missing. Did you try it on a PC? Did it work?

The dowload version is completely inoperable for me unfortunately.

Amazing JOhn - thank you for this. Wow - I am learning how fragile excel is. After reading a lot of comments, Mr. Drew's download does not work a lot of the time. I wish I knew how he did it. Perhaps I'll write him. The sad part is that I won't know which excel version I have at the new job until I get there

but that's just it JOhn - you mentioned VBA macros - I dont know what that is. Does that make the possibility of me learning how to re-create this spreadsheet in the nest 3 weeks more feesable. Do you recommend any resources on how to learn? Thank you.

Ok - I get the basic idea of VBA macros now - makes sense. Of course, I have questions like - what might the command lines look like for all these date equations and how does his "add date" and "add to list" buttons work?

See reply to your other post.


FYI: this is the macro behind the "Add to list" button

Please Login or Register  to view this content.