#VALUE due to wrong entry

[S]

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

 

[Ron Rosenfeld]

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

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

 

[Peo Sjoblom]

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
--


Regards,


Peo Sjoblom

 

[S]

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