Date Value to Code ID

[parvezse16]

Sir, I'm looking for a formula that generates the Date ID from Date value like below in mini sheet, I found a pattern using CountIF on A1&B1 that is 1/1/1900, I don't know how to use this pattern for 05.09.21(todays date) to get a Date like in the below cells.
Thanks in advance

Date ID	Date
8913830B	25.01.21
6018EF9F	23.02.21
A38F431A	25.02.21
2A31AD47	03.03.21
D78B5B8C	10.03.21
61A94CF5	15.03.21
D94D3277	23.03.21
E32C8007	23.04.21
4710767A	03.05.21
9A86CB37	01.06.21
9A86CB37	01.06.21
B15AE204	06.05.21
09304B44	17.09.20
24C1DD4C	03.06.21
5001BBCA	06.08.21
B9D41D5A	11.08.21
65BD1019	13.08.21
02CA0744	16.08.21
BB56EE37	17.08.21
631F012D	23.08.21
631F012D	23.08.21
FB8B4C15	24.08.21
FB8B4C15	24.08.21
51B7885A	30.08.21
1DD5A3BE	16.10.20
1093B676	22.10.20
CB63F58B	27.05.21
FAF145C9	01.09.21
86BA7A29	02.09.21
73ECC05F	03.09.21
3A9F5533	21.10.20

 

[Sektor]

Not clear what you're asking.

 

[parvezse16]

Not clear what you're asking.

Hi Sektor,
There are two columns Date ID and Date, These are already created and I don't have the formula that generates Date ID for 05.09.21 (todays date), so How could I create a formula from these two columns and apply on Today's date.
I hope this is clear, Now. Otherwise you can ask me.
Thanks

 

[jasonb75]

What is the connection between the ID's and dates in the table? The ID's look like hex but they do not correspond to the dates shown.

 

[Peter_SSs]

I'm looking for a formula that generates the Date ID

There are two columns Date ID and Date, These are already created

Sounds contradictory to me.

 

[parvezse16]

What is the connection between the ID's and dates in the table? The ID's look like hex but they do not correspond to the dates shown.

I don't know what exactly the connection between IDs and Dates but I think these are generated using date value, like if you see there are duplicate dates and the ID is also repeated, if there is just a series of IDs than each row has a unique ID

 

[jasonb75]

I think these are generated using date value,

If they were then they would be 4 in the range of AB37 to AE11 for dates in 2020 and 2021. The ID's in your list all convert to values between 8 and 10 digits that bear no resemblance to the dates shown. Date serials in that range are only 5 digits, as are all dates from 17.05.1927 to 14.10.2173

if you see there are duplicate dates and the ID is also repeated

I agree, but unless you know the connection, there is no way of us converting it into a formula.

 

[parvezse16]

If they were then they would be 4 in the range of AB37 to AE11 for dates in 2020 and 2021. The ID's in your list all convert to values between 8 and 10 digits that bear no resemblance to the dates shown. Date serials in that range are only 5 digits, as are all dates from 17.05.1927 to 14.10.2173

The IDs are fixed length of 8 digits, and also dates are 6 digits like dd.mm.yy

 

[jasonb75]

The IDs are fixed length of 8 digits

Correct, but the values that they convert to are 8 to 10 digits.

dates are 6 digits like dd.mm.yy

Date serials are 5 digits, which is what excel would use for the conversion. Based on the format, your dates are more likely to be 10 character text strings which are not valid as dates to convert.

 

[parvezse16]

S

Correct, but the values that they convert to are 8 to 10 digits.

Date serials are 5 digits, which is what excel would use for the conversion. Based on the format, your dates are more likely to be 10 character text strings which are not valid as dates to convert.

Sir, You can change the format as per the excel requirement but keeping the same date with different format not a character/string but a date format.