formula to check if date format is dd/mm/yyyy format

aravindhan_31

Well-known Member
Joined
Apr 11, 2006
Messages
672
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi,

I need your help for a formula,

in cell A1 i have the value 10/01/2015 ( oct 10 2015) ( different dates for about 1000 rows)
i want in column if the format is mm/dd/yyyy , for ex if the some else has entered format as 10/1/2015 then i want to highlight.
this formula is giving to true for both formats. =DATEVALUE(TEXT(A1,"dd/mm/yyyy"))

can someone help me plz
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Such an evaluation would follow your regional settings.

Try rather:

=ISNUMBER(A2)

If the result is TRUE, you have a date that fits your regional settings, otherwise not.

In fact, you can run Data | Text to Columns and choose the format your dates seem to have been inputted and get the right correction.
 
Upvote 0
Thanks Aladin,

The purpose of this is not to change the current format, there is a QC in place where I want to check if column 1 has date in format mm/dd/yyyy ( 10/01/2015) format
even if they have entered mm/d/yyyy (10/1/2015) format, its an error ( logically both dates are correct, my only concern is formatting.

Regards
Arvind
 
Upvote 0
Thanks Aladin,

The purpose of this is not to change the current format, there is a QC in place where I want to check if column 1 has date in format mm/dd/yyyy ( 10/01/2015) format
even if they have entered mm/d/yyyy (10/1/2015) format, its an error ( logically both dates are correct, my only concern is formatting.

Regards
Arvind

If you want to check it whether it's in accordance with your regional settings:

=ISNUMBER(A2)

>> TRUE

That means yes, othwerwise FALSE.

See also Marcelo's suggestion...
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top