need formula to set value based on a date
 

in column O i have a date in format dd/mm/yy. In column P I want to pu
a formula to say:

if O is blank then do nothing

if date in O is not in the past then cell value= number of days fro
today

if date in O IS in the past then cell value = "Expired"

I already have conditional formatting set up to colour the cells i
based on the date, so now I just need to fill them in.

I know its not hugely difficult, I just cant ever get the formulas t
work properly hehe

Thank

--
Message posted from http://www.ExcelForum.com

need formula to set value based on a date
 

Hi
try the following in P1
=IF(O1="","",IF(O1=TODAY(),O1-TODAY(),"Expired"))

-----Original Message-----
in column O i have a date in format dd/mm/yy. In column
P I want to put
a formula to say:

if O is blank then do nothing

if date in O is not in the past then cell value= number
of days from
today

if date in O IS in the past then cell value = "Expired"

I already have conditional formatting set up to colour
the cells in
based on the date, so now I just need to fill them in.

I know its not hugely difficult, I just cant ever get the
formulas to
work properly hehe

Thanks


---
Message posted from http://www.ExcelForum.com/

.

need formula to set value based on a date
 

seems to be almost right, but its putting a date into P1 instead of th
number of days between today and the date that is in O.

Thank

--
Message posted from http://www.ExcelForum.com

need formula to set value based on a date
 

Hi
just format this cell as 'General' ('Format - Cells')

-----Original Message-----
seems to be almost right, but its putting a date into P1
instead of the
number of days between today and the date that is in O.

Thanks


---
Message posted from http://www.ExcelForum.com/

.

need formula to set value based on a date
 

ahh yes thats got it, didnt realise i had the column formatted as
date.

Thank

--
Message posted from http://www.ExcelForum.com

need formula to set value based on a date
 

in column O i have a date in format dd/mm/yy. In column P I want to put
a formula to say:

if O is blank then do nothing

if date in O is not in the past then cell value= number of days from
today

if date in O IS in the past then cell value = "Expired"

I already have conditional formatting set up to colour the cells in
based on the date, so now I just need to fill them in.

I know its not hugely difficult, I just cant ever get the formulas to
work properly hehe

here still another possibility:
Formula in P1:
= If(O1="", "",O1-TODAY())

Use the user-defined numberformat:
0;[<0]"Expired";

--
Mit freundlichen Grüssen

Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)