Date in Formula

[Jan Wachowski]

Problem is

C11 = 31/10/2002
C12 = 212
C14 = C11+C12 (RESULT IS 31/05/2002)
C16 = IF(C4="31/05/2002","31/05/2002",1)

THE RESULT I GET FOR C16 IS 1, WHEN I EXPECT IT TO BE 31/05/2002.

WHAT IS WRONG WITH MY FORMULA?

THANKS JAN

 

[Legacy 98055]

I think change the C4 in your last formula to C14. Assuming that was not just a typo in your post.

Tom

 

[Jan Wachowski]

Thanks Tom

Yes that was a typo and it still does not work.

Any suggestions.

Jan

 

[Larry_S]

Dates are stored as numbers in Excel, and the comparison is comparing the floating point number to the text string in your comparison.

Look up the DateValue function, which converts a character string date into a floating point number that can be used as a date in formulas and compared to dates stored in cells.

 

[DonkeyOte]

Firstly I am presuming that the first date is actually 31/10/01 and you are sinmply adding 212 days to whatever that date is - ie. 31/10/01 plus 212 = 31/05/02

C14 is a date value and thus not text. In your formula for C16 you are trying to argue that C14 is text, it isn't. You could change your formula in one of 2 ways:

1. If(text(c14,"DD/MM/YY")="31/05/02","31/05/02",1)

2. If(c14=37407,"31/05/02",1)
where 37407 represents the date value of 31/05/02.

I would argue that 1 is easier.

 

[Ian Mac]

The best way to do what you need is place the Date your look for in a different cell,
as it's not good practice to Hard Code your variables,
you may need to change them later:

use:

=IF(C14=B16,B16,1)

Where B16 contains the date you want.