Identify Date Format from the date

[ali1985]

Hi Experts,

I have many dates that comes from invoices (all in different formats). Challenge is to know if 03/04/2022 stands for 4th march or 3rd april. Hence, I'm maintaining supplier wise date formats.

However, for a new supplier, since the date format is not maintained in the database, its challenging to move forward.

Is there any formula to paste the date in a cell and get the format of it in its adjacent cell.like:

Input: Date - 25/03/2022
Output that i need: DD/MM/YYYY

Note: there ar more than 50+ date formats. Any other alternate approach will also be appreciated.

 

[SanjayGMusafir]

Check this - I have put same dates in two different formats. But D1, D2, D4, G can be different in your and my part of world depending what default settings your system has. So better test it first and then apply.

All Records.xlsb
	F	G
11	7/4/22	D1
12	07/04/2022	D4
Sheet1

Cell Formulas
Range	Formula
G11:G12	G11	=CELL("format",F11)

 

[ali1985]

Check this - I have put same dates in two different formats. But D1, D2, D4, G can be different in your and my part of world depending what default settings your system has. So better test it first and then apply.

All Records.xlsb
	F	G
11	7/4/22	D1
12	07/04/2022	D4
Sheet1

Cell Formulas
Range	Formula
G11:G12	G11	=CELL("format",F11)

Thanks for your prompt response.

Had 2 doubts:
1- When i m pasting your first date, it automatically gets converted into the 2nd date (22 changing to 2022). If i change the data type to text, then some other date format is getting detected. Is there any work around on this since i will have only 1 cell and in that cell the invoice date will be pasted and it should be pasted as it is

2- from where can i get a mapping table to understand what D1and D4 represent?

 

[SanjayGMusafir]

where can i get a mapping table to understand what D1and D4 represent?

My suggestion is that you use the CELL formula on 5-6 same date entries with different formats and you shall get the answer

What I have understood so far is that D1 is default system date format

 

[SanjayGMusafir]

where can i get a mapping table to understand what D1and D4 represent

Here is the entire list - CELL function