IF FUNCTION QUESTION IN EXCEL 2003

[FRUSTRATED!!!]

I AM CREATING A TRACKING LOG. THE FUNCTION I AM USING IS
=IF(A1-D3>5,"LATE","ON TIME") . A1 IS THE "NOW" FORMULA AND IS MY FIXED
POINT. I WANT IT TO READ THAT IF THE DATE ENTERED IN A SEPARATE COLUMN IS
GREATER THAN 5 DAYS IT IS LATE. HOW CAN I FOMAT AN ENTIRE COLUMN WITHOUT
GOING BLOCK BY BLOCK? WHEN I COPY AND PASTE, I HAVE TO CONSISTENTLY CHANGE
IT TO READ A1, BECAUSE IT WILL REVERT TO WHATEVER LINE I AM ON (A2, A34,
ETC.). i JUST WANT IT TO READ A1. AND IF I CAN GET THE "ON TIME" OR "LATE"
TO POP UP IN GREEN OR RED, THAT WOULD BE AWESOME!!!

 

[FRUSTRATED!!!]

I WOULD ALSO LIKE THE CELL TO BE BLANK IF THERE IS NO VALUE ENTERED IN COLUMN
D.

 

[Rick Rothstein]

For future postings, please turn off your Caps Lock key... text in all caps
is considered shouting and is VERY hard to read.

As for your questions, try this formula...

=IF(D3="","",IF(A$1-D3>5,"LATE","ON TIME"))

Notice for your first question, I prefixed the row number with a $ sign...
that makes that part of the reference absolute, that is non-changing when
copy/pasted... without the $ sign, the reference is relative and, as you
found out, changes when copy/pasted. I handled your second question by
nesting the IF function.

 

[FRUSTRATED!!!]

You are awesome!! I'm sorry for my use of caps, i pretty much leave them on
for all my spreadsheets, and didn't stop to differentiate between the
documents. But, thank you very much for your assistance!

Val

 

[xlmate]

you may use conditional format to show Red for " Late " and Green for " On
Time"

Highlight the column where the result are shown
Go to Format in the Menu Bar
Select Conditional Format
Select Cell Value Is >> equal to >> type in " Late " without quotation marks
Click Format and in Color, select the Red color
click OK
click Add and repeat the above to select Green for " On Time "

hope this help

cheers,

 

[Shane Devenshire]

Hi,

Here is a short version of the formula to do this

=IF(D1,IF(A$1-D1>5,"LATE","ON TIME"),"")

An FYI - the =NOW() function returns the date and time, you seem to be
interested only in dates, so I suggest you change this to =TODAY() If you
use NOW you may get the incorrect answer when the current date is 12/1/2008
and the D1 date is 11/26/08. With these dates the formula returns LATE with
NOW() but ON TIME with TODAY().

If this helps, please click the Yes button

Cheers,
Shane Devenshire