LinkBack URL
About LinkBacks
Hi Guys,
i want to check in Excel Online if in table there valid date format:
10/21/22 6:00 AM
so mm/dd/yy Time
How to do this using conditional formatting?
or formula somehow?
Best,
Jacek
Hi Guys,
i want to check in Excel Online if in table there valid date format:
10/21/22 6:00 AM
so mm/dd/yy Time
How to do this using conditional formatting?
or formula somehow?
Best,
Jacek
Last edited by jaryszek; 12-21-2022 at 10:10 AM.
Please provide an example of an INVALID date format.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
hi Glenn,
every other format than provided is invalid.
Best,
Jacek
It is not possible?
Maybe using Lambda?
Best,
Jacek
How could you possible tell for sure it wasn't in say yy/mm/dd or mm/dd/yy format?
Rory
Hi rorya!
Because this is requirement which i have.
And the format which i provided above is the exact one which has to be written - in other case i should put red as wrong format using conditional formatting.
Maybe this is not possible?
I tried with:
but this is not working.
Please Login or Register to view this content.
I tried also with CELL("Format") but i am getting D2 as result and i want to get D4.
Reference here: https://corporatefinanceinstitute.co...cell-function/
Best,
Jacek
Last edited by jaryszek; 12-23-2022 at 04:20 AM.
Try
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
thanks!
VBA is nice to do but this case this is Excel Online so i could add something like when opening worksheet check all formats and highligh wrong ones.
But prefer to do this without any code.
Best,
Jacek
I think you missed my point. How could you be sure that when they entered say 11/01/23 they meant 1st Nov 2023 and not 11 Jan 2023 or 23 Jan 2011? They all are valid dates and would all appear to match your required format, even though the intentions are different.
thank you Rorya,
i am not sure but they have to input in one allow format which is: 10/21/22 so mm/dd/yyyy (this 6 AM is added automatically by Excel).
Best,
Jacek
I don't understand what you really want, but if you custom format F1 MM/DD/YYYY than =CELL("format",F1) returns D4,
regardless of content.
Even when the field is empty or if you show the content with the TEXT-formula in an other format.
Thanks HansDouwe,
i want to have validation if user inputted date in required format into cell.
If not i should mark this as red.
I was thinkinh about conditional formatting to achive it.
So generally checking if in cell there is date. If yes check if date is in specific format. If no - highlight cell as red.
yes i have noticed. so if you have formatted cells as custom format you will get D4 always.you custom format F1 MM/DD/YYYY than =CELL("format",F1) returns D4,
I tried with applying conditional formatting like in attachment.
Best,
Jacek
Last edited by jaryszek; 12-27-2022 at 05:08 AM.
Hi Jacek,
A formula in Excel cannot directly check in which format exactly the user has entered a date.
You can use a formula to check whether the date is numeric and falls within a desired range.
Hans
Thank you Hans,
so only way to write code in VBA like kvsrinivasamurthy suggested?
Ok so i need Office script for this
Best,
Jacek
Last edited by jaryszek; 12-27-2022 at 05:12 AM.
Update:
there is no way found in Excel to validate format of date in Excel using formula.
But there is solution in VBA using Regex Pattern.
So i assume i could have add also regex pattern in OfficeScripts also.
regex which i can use is:
Best,Please Login or Register to view this content.
Jacek
Last edited by jaryszek; 12-30-2022 at 05:11 AM.
If I had this task where I had to have a specific date format for whatever reason, I would have 4 fields.
In A1 for example I would type the instruction of what I want typed in B1.
"Type Year in Format 'YY'" then put a number restriction on B1 where the number must be equal to or greater than 22 (or whatever year is allowed).
In A2 I would type
"Type the Month Format in 'MM'" then put a number restriction on B2 that the number entered must be an integer 1 to 12.
In A3 I would type
"Type the Day Format in 'DD'" where the integer restriction on B3 would reject based on month number.
Then say in B4 you would say =TEXT(DATE(B1,B2,B3),"MM/DD/YY") and the date will always be verified as a valid date
If you want to get complex, you can have conditional drop downs for the Days that operate based on what month is selected. "If 02 then max number of the drop down would be EOMONTH()"
Mind you this is the direction I would go, but if that solution wouldn't work for you, that's fine, just thought I would mention.
thank you thenew kidd.
It is very good solution. I could break date into small pieces and add validation.
Best,
Jacek
Thank you for the rep,
I just quickly put together an example sheet of what I was visualizing.
my explanation seemed to make it sound more complex than it really was.
Take care.
Thank you,
it is very nice!
Best,
Jacek
Hi,
as an alternative...
Date formatting in the picture is in Turkish.
However, I think you can still give an idea.
The date formats and formulas will be in your regional language when you open the attached file.
Edit : Please press F2 + ENTER when you change the date format in cell G2.
Last edited by 52779; 01-03-2023 at 09:26 PM.
Thank you very much.
O nice it is very nice solution.
It will work also with Excel online?
Best,
Jacek
Last edited by jaryszek; 01-04-2023 at 01:50 AM.
@jaryszek
Thanks for the feedback and rep.
Unfortunately I have no idea about Excel onlineIt will work also with Excel online?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks