Solved how to make if formula for dates

October 21, 2015 at 12:25:07
Specs: Windows 7
Question: I have column "A" with dates that I've sent something out (Lab Sample). Column "B" with the number of BUSINESS DAYS I set for the turnaround time of how fast I need to results. Column "C" with the date that we received the results.

I would like the row to be highlighted if we haven't put in a date in column "C" (received results column) because then I can find out if something has been delayed/Past Due (Then I will clearly see if something is delay and who I need to follow up with)

message edited by ktiefenthaler


See More: how to make if formula for dates

Report •


✔ Best Answer
October 31, 2015 at 20:12:12
(I am assuming that you are familiar with Conditional Formatting. That is what is used to make this work. If not, let us know and we'll explain how to use it.)

I started with this data:

           A             B            C
1
2
3
4     10/16/2015         2                 

I then used this formula to Conditionally Format C4, and chose Red as the CF Fill color.

=AND(TODAY()>WORKDAY($A4,$B4),$C4="")

I then changed my system date to 10/20/2015 and forced a calculation. I did not expect C4 to turn Red and it did not.

I then changed my system date to 10/21/2015 and forced a calculation. I expected C4 to turn Red and indeed it did.

If that process does not work for you, I can't say why since I don't have your spreadsheet to test it on. All I can say is that it works for me as I described above.

Note: The TODAY() function is "volatile" which means it will update whenever the sheet calculates any formula. If you just change your system date to test the CF, it won't do anything unless you force a calculation.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
October 21, 2015 at 13:17:32
If I understand your requirements correctly, it doesn't sound like you need an "IF formula for dates" like your subject line says.

It sounds like the cells in Column C will be blank until you put a date in them, so all you need to do is check and see if the cell in Column C is empty.

Try this:

Select all of the Rows that you want highlighted when Column C is empty. e.g. Rows 3:20

Use the following Conditional Formatting formula:

=$Cx=""

...where x is the number of the first Row in your selection. In this example (Rows 3:20) you would use =$C3=""

Whenever Column C of a given row in your selected range is empty, the formula will return TRUE and the Conditional Formatting will be applied.

However, you may not want to select the entire row. You may just want to select the cells within your table and apply the CF to those cells only. e.g. Select A3:C20 before applying the CF.

Let us know if that works for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
October 21, 2015 at 13:30:53
This helps a little, but I would like the conditional formatting to only apply to the empty boxes that are late (past the given turnaround time that we have input in the adjacent column)

Report •

#3
October 21, 2015 at 16:01:45
That's not what you asked for.

"I would like the row to be highlighted if we haven't put in a date in column "C""

While it is not our job to guess at your actual requirements, I'll give it a try.

You would like the row highlighted if the current date is past the date in Column A plus the number of business days in Column B and Column C is empty.

Am I close?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
October 21, 2015 at 16:21:26
Exactly, sorry for the confusion.

Report •

#5
October 22, 2015 at 05:27:00
Try this as your CF formula:

=AND(TODAY()>WORKDAY($A4,$B4),$C4="")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
October 31, 2015 at 15:55:51
This didn't work,

Example Table
Column A (Date Sent)
Column B ( turnaround time in business days ) Column C (Date received result, which we will input when received)

For COLUMN C (See above)
For Example: If a sample was sent (input into column A) on 10/16/15, Friday (which they'll receive on 10/19/15 because of the weekend) put a turnaround time of "2" (input in column B) Then if we haven't entered a date in "Column C" (date received results) after 10/21/15 I want it to turn RED/FLAG the cell) I want this cell to turn if the date enter is a date after what we specified for our Turn around time (Column B)

message edited by ktiefenthaler


Report •

#7
October 31, 2015 at 20:12:12
✔ Best Answer
(I am assuming that you are familiar with Conditional Formatting. That is what is used to make this work. If not, let us know and we'll explain how to use it.)

I started with this data:

           A             B            C
1
2
3
4     10/16/2015         2                 

I then used this formula to Conditionally Format C4, and chose Red as the CF Fill color.

=AND(TODAY()>WORKDAY($A4,$B4),$C4="")

I then changed my system date to 10/20/2015 and forced a calculation. I did not expect C4 to turn Red and it did not.

I then changed my system date to 10/21/2015 and forced a calculation. I expected C4 to turn Red and indeed it did.

If that process does not work for you, I can't say why since I don't have your spreadsheet to test it on. All I can say is that it works for me as I described above.

Note: The TODAY() function is "volatile" which means it will update whenever the sheet calculates any formula. If you just change your system date to test the CF, it won't do anything unless you force a calculation.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Ask Question