How to format a warning message when two cells in the same row meet a certain criteria?

Hi,

I work in the HR field and am aiming to improve my tracking of information for my candidates. Here I will attach an example of what my excel tracking document looks like for better context.

If you go to the right, you'll see there is a Medical column and to the furthest right is a start date column. Basically, what I'd like to do, is to be able to receive a warning message, or SOME form of warning, should the dates in the medical field and the start date be too close together.

For example, if someone has a medical checkup booked on January 17, but their start date on the furthest column is January 22nd, this is not enough time to receive the results and identify if they are healthy to start work as the reports for the clinic can sometimes take up to 8 business days to arrive.

Is there some way to do this? Ideally, it would be great to get this warning message or pop-up should the start date be 8 business days or less from the medical booking date, however I'm not sure if that's even possible to do on excel. If business days cannot be used, then I guess a simple 8 regular calendar days can work as well.. Or whatever you suggest.

Thank you!

if you want to compare dates, both cell values should be dates. In the sample file I exchanged "booked for [date]" to [date].
Is it possible for you or could there be other text information in these cells? Then it is more complexe but still possible.

There are a lot of "conditional formats" already in. I added one more.

for Range $S:$S -->

Please Login or Register  to view this content.

you can exchange the 8 (red) to any number (working days) as you like.

cheers
Guenter

Hi Guenter,

thank you for your help. Is there a way I can apply formatting such as this to warn me by color-coding the name of the person instead of the start date box? Simply because the name is easier to see as I enter my document. The one I provided you of course is an example and not my actual document and that one has many more columns and start date is at the end.

I've tried entering the same conditional formatting with the formula to the "Name" column instead, but it doesn't work.

Hi, I can't really get that to work.. Would you be able to show me an example on my sheet? Perhaps color-coding the names of the people if the dates conflict?

Hi Guenter,

I've gotten it to work, thank you! I understand how the formula works now.

My only issue with this is that I have one column to show the date (medical), and one column to show the step I am in (status). If I cannot add "Booked for January 22" for example and must only use a date, then it is kind of hard to see at a quick glance if the appointment is booked, requested, or other on "1/22/2020", for example. I hope you know what I mean.

Do you think maybe there's a way to make that more clear? Maybe a formula to add bold to the date in the medical column IF the status next to is indicates that it is booked and not another option?

the problem with "Booked for January 22" is that
- it is a text and must be split and then the date part converted
- you only have the day and month in the text - the year must be added too (always current year - but what happens at the end of the year?)

for separation there is no easy rule available
- the left text part is not of constant length (requested vs booked)
- date part also not (depends on month)

so you can say it may be always the text after the second "space" (blank)

it would be much better to use a date and a extra column in which you have the status of medical (requested, booked, ...). I think you mention this in your reply
"Maybe a formula to add bold to the date in the medical column IF the status next to is indicates that it is booked and not another option?"

If you have this extra column then of course you can add another conditional format to change the font to bold - please take care about the order of conditional formats for and overlapping area. They may override previous ones.

Hi Guenter,

I understand your point of view, I will be adding the year and standardizing the information. My issue is that I am unaware of how to to do a conditional format that would effect the medical column (booking date in bold) BASED on the status of the next column (Status: Appointment Booked). I am truly a "noob", as they say.

Please Login or Register  to view this content.

conditional format is not difficult:
1) select the range you want to apply a rule
2) define the rule (this can be more complex)

I only added to the rule the check that additionally "Appointment Booked" must be true too. I did not change the background color.

If you want the font to be BOLD you either can change the format accordingly or if BOLD should be only on the name and not the date you have to add a new rule (no change in background color but only font)

i hope this helps

Hi Guenter,

greatly appreciate your response. I think I've got the hang of the first one now! Let me see if I understand..

Formula to format text of name of candidate based on medical booking date being less than 8 business days from the start date:

=AND(ISNUMBER($S1),WORKDAY($S1,-8)<$N1)

Then add the format you want for the text.

This is concerning my first question, this I now get.

However, when it concerns the second question regarding formatting the medical booking date in bold only if the next column says "appointment booked" and NOT "appointment requested", etc, I am not clear.

I believe that your most recent answer combined these two functions together, whereas I wanted them seperate.. Apologies.. I would like to both be able to add a warning on my candidate name when they are booked less than 8 business days from the start date, AND separately, bold the appointment date of the medical column only when the next column indicates that it is booked. This is because since I cannot write "Booked for January 23" anymore, for example, to make the first formula work on the candidate name, I must only write the date from now on and nothing else. I want to make it obvious when an appointment is booked VS when it is requested, for example, since I cannot write "Booked for.." anymore.

I hope you understand and I am sorry to ask so much of you.

One last thing; I can see that I can modify the name of the candidate's text and the cell based on the first formula we discussed regarding a warning if they are within those 8 days, but is it possible to add an icon instead of modifying the name? Such as a red X or something like that (such as in conditional formatting > icon sets)?

Thank you

A lot to answer :-)

You are right, I combined both into one.

next column says "appointment booked" and NOT "appointment requested"

the formula says that anything else except "appointment booked" is false and will not get a different format.
You can select also other items in your drop down box, they all will result in false.

You are completely in your table :-) but I only see a lot of columns with data. There are 4 x "Status" and I only can assume that they belong to the left column. Therefore I do not understand why "Booked for [date]" is a problem because I thought the next column to the right already indicates the belonging status (e.g. requested, booked,...). May be I am wrong?

don't worry about asking! I will be available one more day, then I am off for one week.

the last thing: you can of course use icons for conditional formats but there is a limitation - they are only possible for values (with two or three limits) but not for text.

It would be much easier to understand your requests if you attach a file
- each row one example and format (not conditional) the cells as you expect the result (describe which rule should be applied)
- describing column names may not be sufficient. Much easier is to understand if you add also "column A", "B"... (especially when you have 4x "Status")

I added a conditional format for the date in Column N (Medical) - see attachment

Hi Guenter,

here is an example document. I cannot attach the actual document since it belongs to the company and has confidential information.

Please see attached. As you can see, I would like the name of the candidate to show up in red (red fill) when their medical booking date is less than 8 business days from their start date, as we had discussed.

SEPERATELY, please see the date in bold in the medical column; based off of the text of the column to the right (appointment booked), the text is bolded. If the next column had said "appointment requested",
such as the rows below, then it should NOT be in bold. The bold is simply there to make it more obvious that the appointment is booked, since now I cannot write "Booked for..." anymore because of the formula
for the formatting of the name based on the start date requiring a regular date in date format (1/24/2020) to work.

I hope you understand what I mean. As I said I think I understand how to get the formatting of the name done, but now need your help for the "Appointment booked" and the date to bold.

If the formatting of the name based off of the start date could be done with a formula while still being able to write "Booked for January 24" instead of simply "1/24/2020" in the medical
column, then I would not need this second formula, however as you've pointed out it's a lot more complicated to format something based off of text and a date.

Oh, and also, I just tried applying this formula: =AND(ISNUMBER($W1),WORKDAY($W1,-8)<$Q1)

To make the name of the candidate fill with red if the start date is closer than 8 business days from the medical booking date, but I noticed it also applies the formatting when the medical column does not have a date in it at all.

Once the appointment has been finished and the results are received, I typically change the date in the medical column to say "results received" or something like that. And this still applies the formatting?

Is there a way to avoid formatting if medical column says anything other than a date?

If you have an appointment but no medical is this always a concern or just a concern if it is less than 8 days to the start date?

You should attach a sheet with the results you want manually shown on the sheet for all the circumstances you can see happening

Hi, I am not sure what you mean davsth. The only concern is if the medical is booked less than 8 business days before the start date, as it can take up to 8 business days for me to receive the results, which we need in-hand before the employee begins work. Now, of course, if the employee is starting in 6 business days and the appointment isn't even booked yet, that's a concern, but that has never happened and I am not concerned with that.

Hello Nicolas,

"Name"
select Range: $C:$C Formula:

Please Login or Register  to view this content.

"Medical"
select Range: $Q:$Q Formula:

Please Login or Register  to view this content.

please be aware that if you add or delete some columns (the last file has different columns compared to the previous example files) then you have to adopt the formulas first.

I included the formats in the attachment

cheers Guenter

Hi Guenter,

thanks for the followup. I see that according to your example this works; however in my document I don't always have a date in the medical column. When the appointment is completed and the results are in, I usually write "Fit for work" or something like that. In such cases the red formatting still applies even though there's no date or anything so there's no booking. I wonder how I can fix that?

When the appointment is completed and the results are in, I usually write "Fit for work" or something like that

why do you write this into the "medical" column and not into the corresponding status column?