Formula to validate an input date

[The_Snook]

Morning all,

I've a document where you input the components of a date (day, month & year) into 3 different cells (A1, B1 & C1). How can I check that the date input is valid?

NB - The values input are all numeric.

Thanks in advance,

Snook

[davsth]

what is valid? Just a real dates

you can not know if the date is valid until all 3 boxes are complete, so data validation will not work (except preventing days above 31, months above 12 etc


so an if statement or conditional formatting can flag iserror(date(c1,b1,a1))


An attachment makes life so much easier

[Richard Buttrey]

Hi,

You could use another cell to check that each of the three cells are numeric with =ISNUMBER() but there are better ways.

Why not just have a single cell to capture a date and make that a Data Validation cell that will only accept a date.

Alternatively use a pop up calendar or UserForm that only allows the user to select a date. I'm attaching a file containing an example of the latter.
A1 has the name 'startdate'

Clicking this cell causes the form to pop up.

[etaf]

you could use
=DATEVALUE($A1&"/"&$B1&"/"&$C1)
in a datavalidation

BUT that will allow any year , as that will be a date
you could add another validation for the year to be greater that a number like 2019

[The_Snook]

Afternoon all,

Apologies for the confusion, I've uploaded an example of what I'm working with. Essentially I require an output cell that validates the date formulated from the 3 input cells.

I've added data val to the relevant cells to restrict the values input.

My hands are tied on the structure as I've been asked to replicate a current online template.

Thanks again,

Snook

[AliGW]

Given the data validation you have on those fields, how could any input be invalid?

[etaf]

slight modification to my data validation suggestion
=AND(NOT(ISERROR(DATEVALUE($B3&"/"&$C3&"/"&$D3))), $D3>=2020)

your validation means that the 31 feb is possible
or 31 september is possible

But using the datevalue - has to be a valid date - so will account for leap years and 30/31 for most months and 28/29 for feb

[The_Snook]

Lol, good point.

If a formula based check is available I'd be grateful of it, as it'll keep the powers that be happy if I can visually show that each element of the template is being checked.

Plus, never underestimate the end user's ability to butcher a data input template! Someone will copy and paste 'July' over the month field, I can see it now.

Snook

[The_Snook]

Nice one etaf, bang on the money!

Thanks all,

Snook

[CK76]

To ensure that date string construct works across different system/region setting.
You should use universally accepted pattern, yyyy-mm-dd. dd/mm/yyyy pattern will fail on US region setting.

Replace Datevalue portion of etaf's formula to...
DATEVALUE(D3&"-"&$C3&"-"&$B3)