#VALUE due to wrong entry

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

S - 01 Nov 2007 21:36 GMT

This formula represents dates dd mm yyyy over 3 seperate cells and
deducted from a similar formula to work out how many weeks at £s per week.
=IF(DATE(K7,J7,I7)-DATE(G7,F7,E7)>=6,229,0

It is used as part of a spreadsheet to work out a monetry deposit for
rental.
However when `renters` complete this all is ok unless they dont complete as
mm (07) but instead type the month as in `July`
This then returns #VALUE in the destination cell .
Is there a way to change the formula to allow for the `renters` not
completing as requested (mm), but still return £s rather than #VALUE

I hope I have explained clearly.
thanks

Reply to this Message

Ron Rosenfeld - 01 Nov 2007 21:55 GMT

>This formula represents dates dd mm yyyy over 3 seperate cells and
>deducted from a similar formula to work out how many weeks at £s per week.
[quoted text clipped - 10 lines]
>I hope I have explained clearly.
>thanks

If your Windows/Control Panel/Regional Settings for Short Date is MDY, then
try:

=DATEVALUE(J7&"-"&I7&"-"&K7)

Note that I,J and K are in the MDY order

--ron

Reply to this Message

Peo Sjoblom - 01 Nov 2007 22:06 GMT

Maybe something like

=IF(COUNT(E7:G7,I7:K7)<>6,0,IF(DATE(K7,J7,I7)-DATE(G7,F7,E7)>=6,229,0))

You might to look up data > validation, there you can make sure that you
cannot type in text in those cells

Signature

Regards,

Peo Sjoblom

> This formula represents dates dd mm yyyy over 3 seperate cells and
> deducted from a similar formula to work out how many weeks at £s per week.
[quoted text clipped - 10 lines]
> I hope I have explained clearly.
> thanks

Reply to this Message

S - 01 Nov 2007 22:17 GMT

Thanks to both for a quick reponse.
I have used the `validation` route that should hopefully sort it or at least
alert them that its not correct.

thank you

> Maybe something like
>
[quoted text clipped - 17 lines]
>> I hope I have explained clearly.
>> thanks

Reply to this Message