Date Value to Code ID

P

parvezse16

New Member
Joined
Sep 5, 2021
Messages
14
Office Version
  1. 2013
Platform
  1. Windows
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 IDDate
8913830B25.01.21
6018EF9F23.02.21
A38F431A25.02.21
2A31AD4703.03.21
D78B5B8C10.03.21
61A94CF515.03.21
D94D327723.03.21
E32C800723.04.21
4710767A03.05.21
9A86CB3701.06.21
9A86CB3701.06.21
B15AE20406.05.21
09304B4417.09.20
24C1DD4C03.06.21
5001BBCA06.08.21
B9D41D5A11.08.21
65BD101913.08.21
02CA074416.08.21
BB56EE3717.08.21
631F012D23.08.21
631F012D23.08.21
FB8B4C1524.08.21
FB8B4C1524.08.21
51B7885A30.08.21
1DD5A3BE16.10.20
1093B67622.10.20
CB63F58B27.05.21
FAF145C901.09.21
86BA7A2902.09.21
73ECC05F03.09.21
3A9F553321.10.20
 
Sort by date Sort by votes
Sir, How about this dates format

Date IDDate
8913830B25-01-2021
6018EF9F23-02-2021
A38F431A25-02-2021
2A31AD4703-03-2021
D78B5B8C03-10-2021
61A94CF515-03-2021
D94D327723-03-2021
E32C800723-04-2021
4710767A05-03-2021
9A86CB3706-01-2021
9A86CB3706-01-2021
B15AE20405-06-2021
09304B4417-09-2020
24C1DD4C06-03-2021
5001BBCA08-06-2021
B9D41D5A08-11-2021
65BD101913-08-2021
02CA074416-08-2021
BB56EE3717-08-2021
631F012D23-08-2021
631F012D23-08-2021
FB8B4C1524-08-2021
FB8B4C1524-08-2021
51B7885A30-08-2021
1DD5A3BE16-10-2020
1093B67622-10-2020
CB63F58B27-05-2021
FAF145C909-01-2021
86BA7A2909-02-2021
73ECC05F09-03-2021
3A9F553321-10-2020
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
It doesn't matter how you format the date, excel will always work from the serial number. What I said in post 7 still applies.

The table below shows what your ID's convert into, there is nothing that resembles a date anywhere. Unless you can tell us exactly how the original ID's were created from the dates shown in your table then there is nothing we can do.
Book1
AB
1Date IDDate
28913830B2299757323
36018EF9F1612246943
4A38F431A2744075034
52A31AD47707898695
6D78B5B8C3616234380
761A94CF51638485237
8D94D32773645715063
9E32C80073811344391
104710767A1192261242
119A86CB372592525111
129A86CB372592525111
13B15AE2042975523332
1409304B44154159940
1524C1DD4C616684876
165001BBCA1342290890
17B9D41D5A3117686106
1865BD10191706889241
1902CA074446794564
20BB56EE373143036471
21631F012D1662976301
22631F012D1662976301
23FB8B4C154220210197
24FB8B4C154220210197
2551B7885A1370982490
261DD5A3BE500540350
271093B676278115958
28CB63F58B3412325771
29FAF145C94210116041
3086BA7A292260367913
3173ECC05F1944895583
323A9F5533983520563
Sheet1
 
Upvote 0
jasonb75 said:
It doesn't matter how you format the date, excel will always work from the serial number. What I said in post 7 still applies.

The table below shows what your ID's convert into, there is nothing that resembles a date anywhere. Unless you can tell us exactly how the original ID's were created from the dates shown in your table then there is nothing we can do.
Book1
AB
1Date IDDate
28913830B2299757323
36018EF9F1612246943
4A38F431A2744075034
52A31AD47707898695
6D78B5B8C3616234380
761A94CF51638485237
8D94D32773645715063
9E32C80073811344391
104710767A1192261242
119A86CB372592525111
129A86CB372592525111
13B15AE2042975523332
1409304B44154159940
1524C1DD4C616684876
165001BBCA1342290890
17B9D41D5A3117686106
1865BD10191706889241
1902CA074446794564
20BB56EE373143036471
21631F012D1662976301
22631F012D1662976301
23FB8B4C154220210197
24FB8B4C154220210197
2551B7885A1370982490
261DD5A3BE500540350
271093B676278115958
28CB63F58B3412325771
29FAF145C94210116041
3086BA7A292260367913
3173ECC05F1944895583
323A9F5533983520563
Sheet1
Click to expand...
Sir, If I know the origin/creation of how IDs were created than I know the algorithm, and I can do for rest. But I don't know.
 
Upvote 0
jasonb75 said:
It doesn't matter how you format the date, excel will always work from the serial number. What I said in post 7 still applies.

The table below shows what your ID's convert into, there is nothing that resembles a date anywhere. Unless you can tell us exactly how the original ID's were created from the dates shown in your table then there is nothing we can do.
Book1
AB
1Date IDDate
28913830B2299757323
36018EF9F1612246943
4A38F431A2744075034
52A31AD47707898695
6D78B5B8C3616234380
761A94CF51638485237
8D94D32773645715063
9E32C80073811344391
104710767A1192261242
119A86CB372592525111
129A86CB372592525111
13B15AE2042975523332
1409304B44154159940
1524C1DD4C616684876
165001BBCA1342290890
17B9D41D5A3117686106
1865BD10191706889241
1902CA074446794564
20BB56EE373143036471
21631F012D1662976301
22631F012D1662976301
23FB8B4C154220210197
24FB8B4C154220210197
2551B7885A1370982490
261DD5A3BE500540350
271093B676278115958
28CB63F58B3412325771
29FAF145C94210116041
3086BA7A292260367913
3173ECC05F1944895583
323A9F5533983520563
Sheet1
Click to expand...
I did same converted Hex2Dec, and I consider that number as timestamp and I have applied formulas but I didn't get the right answer
Please review it if you could find a relation now.

DateCodeTimeStampDate
25.01.218913830B
2299757323​
11/16/2042​
23.02.216018EF9F
1612246943​
2/2/2021​
25.02.21A38F431A
2744075034​
12/15/2056​
03.03.212A31AD47
707898695​
6/7/1992​
10.03.21D78B5B8C
3616234380​
8/4/2084​
15.03.2161A94CF5
1638485237​
12/2/2021​
23.03.21D94D3277
3645715063​
7/11/2085​
23.04.21E32C8007
3811344391​
10/10/2090​
03.05.214710767A
1192261242​
10/13/2007​
01.06.219A86CB37
2592525111​
2/26/2052​
01.06.219A86CB37
2592525111​
2/26/2052​
06.05.21B15AE204
2975523332​
4/15/2064​
17.09.2009304B44
154159940​
11/20/1974​
03.06.2124C1DD4C
616684876​
7/17/1989​
06.08.215001BBCA
1342290890​
7/14/2012​
11.08.21B9D41D5A
3117686106​
10/17/2068​
13.08.2165BD1019
1706889241​
2/2/2024​
16.08.2102CA0744
46794564​
6/26/1971​
17.08.21BB56EE37
3143036471​
8/6/2069​
23.08.21631F012D
1662976301​
9/12/2022​
23.08.21631F012D
1662976301​
9/12/2022​
24.08.21FB8B4C15
4220210197​
9/26/2103​
24.08.21FB8B4C15
4220210197​
9/26/2103​
30.08.2151B7885A
1370982490​
6/11/2013​
16.10.201DD5A3BE
500540350​
11/11/1985​
22.10.201093B676
278115958​
10/24/1978​
27.05.21CB63F58B
3412325771​
2/17/2078​
01.09.21FAF145C9
4210116041​
6/1/2103​
02.09.2186BA7A29
2260367913​
8/17/2041​
03.09.2173ECC05F
1944895583​
8/19/2031​
21.10.203A9F5533
983520563​
3/2/2001​
07.09.219E16ADDF
2652286431​
1/17/2054​
 
Upvote 0
jasonb75 said:
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.
Click to expand...
Sir, That is a timestamp value in CEST, could you convert that into with corresponding date. Please
 
Upvote 0
If they are timestamps then they have not been created using a method that I've seen previously and there appears to be no obvious pattern of any kind. As I said before, Unless you can tell us how they were created, it is unlikely that we will be able to help.
 
Upvote 0
Is this what you want
+Fluff 1.xlsm
BC
1Code
28913830B16/11/2042
36018EF9F02/02/2021
4A38F431A15/12/2056
52A31AD4707/06/1992
6D78B5B8C04/08/2084
761A94CF502/12/2021
8D94D327711/07/2085
9E32C800710/10/2090
104710767A13/10/2007
119A86CB3726/02/2052
129A86CB3726/02/2052
13B15AE20415/04/2064
1409304B4420/11/1974
1524C1DD4C17/07/1989
165001BBCA14/07/2012
17B9D41D5A17/10/2068
1865BD101902/02/2024
1902CA074426/06/1971
20BB56EE3706/08/2069
21631F012D12/09/2022
22631F012D12/09/2022
23FB8B4C1526/09/2103
24FB8B4C1526/09/2103
2551B7885A11/06/2013
261DD5A3BE11/11/1985
271093B67624/10/1978
28CB63F58B17/02/2078
29FAF145C901/06/2103
3086BA7A2917/08/2041
3173ECC05F19/08/2031
323A9F553302/03/2001
339E16ADDF17/01/2054
34
Main
Cell Formulas
RangeFormula
C2:C33C2=(HEX2DEC(B2)/86400)+25569
 
Upvote 0
@Fluff, I think that you will find the 2 columns on the right of the table to be the OP's formula attempt. They're trying to get the dates on the left from the codes in the table.
 
Upvote 0
If that's the case I don't think the OP has a chance. Those codes look as though they are a hex equivalent of a Unix timestamp.
If those codes do represent the dates on the left, then they cannot be simple hex code as there is no correlation.
+Fluff 1.xlsm
ABCD
1DateCode
2223/08/2021631F012D1662976301
2324/08/2021FB8B4C1542202101972557233896
2901/09/2021FAF145C94210116041
3002/09/202186BA7A292260367913-1949748128
3103/09/202173ECC05F1944895583-315472330
Main
Cell Formulas
RangeFormula
D23,D30:D31D23=C23-C22
C22:C23,C29:C31C22=HEX2DEC(B22)
 
Upvote 0
jasonb75 said:
If they are timestamps then they have not been created using a method that I've seen previously and there appears to be no obvious pattern of any kind. As I said before, Unless you can tell us how they were created, it is unlikely that we will be able to help.
Click to expand...
Sir, the origin of creation is in invoice, I'm uploading image but a 500KB is too large for this server.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

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