I AM CREATING A TRACKING LOG. THE FUNCTION I AM USING IS
=IF(A1-D35,"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!!!

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

"FRUSTRATED!!!" wrote:

I AM CREATING A TRACKING LOG. THE FUNCTION I AM USING IS
=IF(A1-D35,"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!!!

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-D35,"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.

--
Rick (MVP - Excel)


"FRUSTRATED!!!" wrote in message
...
I WOULD ALSO LIKE THE CELL TO BE BLANK IF THERE IS NO VALUE ENTERED IN
COLUMN
D. :)

"FRUSTRATED!!!" wrote:

I AM CREATING A TRACKING LOG. THE FUNCTION I AM USING IS
=IF(A1-D35,"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!!!

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

"Rick Rothstein" wrote:

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-D35,"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.

--
Rick (MVP - Excel)


"FRUSTRATED!!!" wrote in message
...
I WOULD ALSO LIKE THE CELL TO BE BLANK IF THERE IS NO VALUE ENTERED IN
COLUMN
D. :)

"FRUSTRATED!!!" wrote:

I AM CREATING A TRACKING LOG. THE FUNCTION I AM USING IS
=IF(A1-D35,"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!!!

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,




"FRUSTRATED!!!" wrote:

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

"Rick Rothstein" wrote:

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-D35,"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.

--
Rick (MVP - Excel)


"FRUSTRATED!!!" wrote in message
...
I WOULD ALSO LIKE THE CELL TO BE BLANK IF THERE IS NO VALUE ENTERED IN
COLUMN
D. :)

"FRUSTRATED!!!" wrote:

I AM CREATING A TRACKING LOG. THE FUNCTION I AM USING IS
=IF(A1-D35,"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!!!

Hi,

Here is a short version of the formula to do this

=IF(D1,IF(A$1-D15,"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



"FRUSTRATED!!!" wrote:

I AM CREATING A TRACKING LOG. THE FUNCTION I AM USING IS
=IF(A1-D35,"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!!!