DATES,TEXT AND CONDITIONAL FORMATTING IN COLUMNS

[Scgordon]

I HAVE TWO COLUMNS IN DATE FORMAT. COLUMN A IS COMPLETELY FILLED WITH DATES AND COLUMN B HAS SOME DATES AND BLANKS.. I INSERTED COLUMN C AND PUT =TODAY()-A1 SO THAT I WOULD KNOW HOW MANY DAYS BETWEEN THE FIRST DATE AND THE PRESENT DATE. I WANT TO ENTER IN COLUMN B WHERE THERE ARE BLANKS TEXT INDICATING "OVER DUE 90 DAYS" ONLY IF COLUMN C IS OVER


I TRIED =IF(AND(C1>90,B1=""),"OVERDUE 90 DAYS","")


WHAT IT RETURNS IS A ZERO. COLUMN B IS IN DATE FORMAT. CAN ANYONE HELP?


I WANT TO SUBTRACT TWO DATES AND IF IT IS OVER 90, TO ENTER IN THE BLANK CELL OF COLUMN B OVERDUE 90 DAYS, IF NOT LEAVE IT BLANK.

 

[Hui]

Scgordon


Firstly, Welcome to the Chandoo.org Forums


You can't have a formula in B1 that refers to itself

I would use


B1: =IF(C1>90,"Overdue 90 Days","")

copy down


Now if you need to retain the Dates that are already in B1?

put this in D1 and copy down


D1: =IF(and(C1>90,B1=""),"Overdue 90 Days","")

copy down

 

[Scgordon]

FIRST OF ALL I WANT TO THANK YOU. THE FIRST OPTION DID NOT WORK AS WHEN I COPIED DOWN, IT DELETED THE DATES THAT WERE ALREAY IN COLUMN B. I DID ENTER INTO COLUMN D AND IT WORKS...I WILL HIDE COLUMN C AND NOBODY WILL SEE IT.


BUT IS THERE A WAY FOR COLUMN B'S DATES BE NOT AFFECTED? IT CHANGE THE DATE THAT WAS THERE TO OVER DUE 90 DAYS.


LETS SEE IF I STATE THIS CORRECTLY: COLUMN A IS COMPLETELY FILLED IN WITH A DATE IN EACH ROW AND SOME OF COLUMN B HAS SOME DATES IN ITS ROWS. I DID THE TODAY FUNCTION IN COLUMN C AND SUBTRACTED FROM COLUMN A, TELLING ME HOW MANY DAYS HAV E LASPED. WHAT I WANT IS FOR THE CELLS THAT ARE EMPTY IN COLUMN B TO SAY "OVER DUE 90 DAYS, ONLY IF COLUMN C IS OVER 90 DAYS. I DO NOT WANT FOR THE DATES THAT ARE ALREAY IN COLUMN B BE AFFECTED.


IF C>90 AND B IS BLANK, B SHOULD SAY OVERDUE IF NOT LEAVE BLANK SO A DATE COULD BE FILLED IN.


GREATLY APPRECIATED AND THANKS.

]

]

 

[Hui]

Scgordon


This can't be done without VBA


If the values in Column B have a formula it could be done in Column B, but you haven't said if that is the case.


VBA Solution


Goto the VBA Editor (Alt F11)

Double Click the worksheet your working on

Copy and paste the following code into the pane on the right

Run it, F5

[pre]

Sub Overdue()
Dim x As Integer
For x = 1 To Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Rows.Count
If Cells(x, 2).Value = "" And Cells(x, 3).Value >= 90 Then Cells(x, 2).Value = "Overdue"
Next x
End Sub

[/pre]

 

[asa]

Couldn't a conditional formatting rule on column B that uses a format with the message "OVERDUE" in it under that condition, and that otherwise uses the normal date format work?

 

[asa]

I retract my comment.

It would work but for one little problem... number formats (which allow you to specify text) only apply to cells with values in them. You cannot "format" a blank cell.


You could use conditional formatting to highlight the cell (or even the row) red (or apply other border or fill formatting) if those two conditions are met, or you could have the "OVERDUE" message be calculated by a formula and stored in a separate column from where the data entry occurs, or you could use Hui's solution that exactly meets your request


Asa


EDIT..a few more details on options available.

 

[magbo]

Hi,

there is a simple way to copy a formula to blank cells in column B.

1. Input into B1 a formula "=IF(C1>90,"OVERDUE 90 DAYS","")"

2. select B1 and press Ctrl + C

3. select column B

4. choose menu Edit -> Go to -> Special -> Blank and click OK

5. Paste

Or look here

http://www.contextures.com/xlDataEntry02.html

and similar (not exactly the same ) problem:

http://www.launchexcel.com/fill-in-blank-cells-from-above/