Hi!
Need some help.
I created some sort of Date Picker from 3 cells (A1: date 1-31, A2: month 1-12, and A3: years) dropdowns.
Using Formula in a A4: =IFERROR(DATE(A1,A2,A3),"Not a proper date")
Formula works as such, but if I pick from dropdowns 30.feb.2019, the formula returns 01.mar.2019...
How can I fix this so i get in return that the date is not correct?
Sadly on work PC I'm not allowed to use VBA or any add-ons.
Excel will add the extra days so there is no error.
Possibly an additional formula in B4 for example?
Please Login or Register to view this content.
Date value is stored as integer value in Excel.
EDIT: So each integer value supplied to DATE() function will be converted to # of days first and added to form date value. Hence any overflow in Month/Day argument, just adds extra days to resulting date.
EDIT2: Year argument has bit of special treatment. Values between 1-1899 is simply added to 1900. Values > 1900 is used as is. This is due to Excel's minimum date starting from 1/1/1900.
Meaning, any combination of integer values in Cell A1, A2, A3 will result in valid date.
Instead of using DATE() function, try using DATEVALUE.
Ex:
Formula:
Please Login or Register to view this content.
DATEVALUE converts string into date and will require that string as is, represent valid date.
Last edited by CK76; 09-11-2019 at 09:51 AM. Reason: Added explanations
?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
― Robert A. Heinlein
Thanks, CK76!
That's what I was looking for, and works for me just fine.
Just - added "&" for the A1 cell in the formula.
=IFERROR(DATEVALUE(A3&"-"&A2&"-"&A1),"Not a proper date")
Ah, forgot that when I typed it in.
You are welcome and thanks for the rep
If your question is answered, please mark the thread as solved by using thread tool found at top of your initial post.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks