If Formula using greater than Date

[James8761]

Hi,
I'm trying to do something that I feel should be simple but I can't get the formula correct.

The highlighted formula is in Cell FS17. I'm trying to get the answer to be £417 (£90000/216) but it keeps saying £0.
Any ideas please?

 

[Joe4]

If you enter these two formulas in any blank cells, what do they return?
=ISNUMBER(FS11)
=ISNUMBER(T17)

If either one (or both) of those formulas return FALSE, then those entries are in fact text entries, and NOT valid date entries, so you cannot do date math on them straight up without either fixing the entries, or converting them to valid dates in the formula.

 

[James8761]

If you enter these two formulas in any blank cells, what do they return?
=ISNUMBER(FS11)
=ISNUMBER(T17)

If either one (or both) of those formulas return FALSE, then those entries are in fact text entries, and NOT valid date entries, so you cannot do date math on them straight up without either fixing the entries, or converting them to valid dates in the formula.

Hmm... thank you, interesting.
They are indeed different, top one TRUE, bottom one FALSE.
When I click on format cells though they are both formatted as such

 

[James8761]

If you enter these two formulas in any blank cells, what do they return?
=ISNUMBER(FS11)
=ISNUMBER(T17)

If either one (or both) of those formulas return FALSE, then those entries are in fact text entries, and NOT valid date entries, so you cannot do date math on them straight up without either fixing the entries, or converting them to valid dates in the formula.

I just entered the DATEVALUE in a blank column and all seems to work. Thanks for the input.

 

[Joe4]

Checking the format of the cell really doesn't help. You can apply a custom format to any cell, but they only affect valid numeric entries.
If you try to apply them to text entries, they do not do anything).
(Note that dates are really just numbers, specifically the number of data since 1900 - you can see this by changing the format of any date cell to General, and see the number it shows you!).

Note that you do not need a separate column. You can handle it right in the formula, i.e.
if T17 was the cell entered as text, then your formula could start:

=IF($FS$11>DATEVALUE($T17),...

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[James8761]

Checking the format of the cell really doesn't help. You can apply a custom format to any cell, but they only affect valid numeric entries.
If you try to apply them to text entries, they do not do anything).
(Note that dates are really just numbers, specifically the number of data since 1900 - you can see this by changing the format of any date cell to General, and see the number it shows you!).

Note that you do not need a separate column. You can handle it right in the formula, i.e.
if T17 was the cell entered as text, then your formula could start:

=IF($FS$11>DATEVALUE($T17),...

Thank you for that, always good to learn something new.