# DATE FORMATTING



## handri (Jan 3, 2023)

Hi 

i failed to change the fromat from 221124 TO 241122 (DD,MM.YY)

Thanks

TSB BARCODE 2023 - Life Span.xlsxJKLM5DATE CODEDATEDATE CONVERTEXP.DATE6L2211240261022112431,05,250530/11/2505NEW Data TSB SCANNERCell FormulasRangeFormulaK6K6=MID(J6,2,6)L6L6=TEXT(K6,"dd,mm,yyyy")M6M6=EDATE(K6,6)Cells with Conditional FormattingCellConditionCell FormatStop If TrueM2,H1,H3:H1048576,J1:M1,I6:I2785,N6:N2785,J3:M1048576Cell Valuecontains "NOT IN DATABASE"textNOM2,H1,H3:H1048576,J1:M1,I6:I2785,N6:N2785,J3:M1048576Cell Valuecontains "NOT IN DATABASE"textNO


----------



## Peter_SSs (Jan 3, 2023)

Is this what you are trying to do?

23 01 03.xlsmJKL5DATE CODEDATEDATE CONVERT6L22112402610221124241122DateCell FormulasRangeFormulaK6K6=MID(J6,2,6)L6L6=CONCAT(MID(K6,{5,3,1},2))


----------



## Peter_SSs (Jan 3, 2023)

.. or if you wanted to go directly from column J

23 01 03.xlsmJL5DATE CODEDATE CONVERT6L22112402610241122DateCell FormulasRangeFormulaL6L6=CONCAT(MID(J6,{6,4,2},2))


----------



## handri (Jan 3, 2023)

Peter_SSs said:


> =CONCAT(MID(J6,{6,4,2},2))


done sir but the EXP DATE not working

thanks

TSB BARCODE 2023 - Life Span.xlsxJKL5DATE CODEDATE CONVERTEXP.DATE6L221124026102411221/9/60NEW Data TSB SCANNERCell FormulasRangeFormulaK6K6=CONCAT(MID(J6,{6,4,2},2))L6L6=EDATE(K6,6)Cells with Conditional FormattingCellConditionCell FormatStop If TrueL2,H1,H3:H1048576,I6:I2785,M6:M2785,J1:L1,J3:L1048576Cell Valuecontains "NOT IN DATABASE"textNOL2,H1,H3:H1048576,I6:I2785,M6:M2785,J1:L1,J3:L1048576Cell Valuecontains "NOT IN DATABASE"textNO


----------



## Alex Blakenburg (Jan 3, 2023)

Leveraging off Peter's formula how about:
(Change the Date format to what you want using the normal number formatting dialogue box)

Book2JKL5DATE CODEDATE CONVERTEXP.DATE6L2211240261024/11/202224/05/2023Sheet1Cell FormulasRangeFormulaK6K6=DATEVALUE(TEXTJOIN("/",,(MID(J6,{6,4,2},2))))L6L6=EDATE(K6,6)


----------



## handri (Jan 3, 2023)

Alex Blakenburg said:


> Leveraging off Peter's formula how about:
> (Change the Date format to what you want using the normal number formatting dialogue box)
> 
> 
> ...


thanks


DATE CODEDATE CONVERTEXP.DATEL221106000276/11/20226/5/20231D20221130#VALUE!#VALUE!


----------



## Peter_SSs (Jan 3, 2023)

Previously you gave us *one* example that had *one *letter at the beginning before the date. Now there are two characters. Can there be more than two or less than one? If so please give a *variety *of examples *and the expected results*.

Previously you gave us *one *example where the date part was 6 digits (221124) where (we are guessing because you did not specifically tell us) the first 2 digits were year, the next 2 digits were month and the last 2 digits were day. If we apply that same reasoning to this extra *one *example then the date part presumably (you haven't actually told us) would be 202211 which would mean the month part is 22. Since that cannot be a month number how would we know what result you are expecting for that example?

*If *the date part of this latest example is actually 20221130 where the year is 4 digits (2022) moth is 2 digits (11) and day is 2 digits (30), *how* would we decide for other examples whether the year was 2 digits or 4 digits?

So, repeating what I have already said please give a *variety *of examples *and the expected results*.
You also need to *explain in words* what the logic is for getting the expected results.


----------



## handri (Jan 3, 2023)

Hi Pete



I’m apologise due my confusing post before. What I want to do is to transfer two type of unreadable date to readable date.

Such as below.



J column k column

Unreadable date Readable date



The problem is need to remove text from two type of source into common date

There will 2 types of source which will be put in K COLUMN.



E.G



J column                    k column

TYPE 1 source         NEED DATE

L22110600026  to  6/11/2022



TYPE 2 source

1D20221130  to   30/11/2022



I need to converting every single of this 2 type into need date as mentioned.

All RESULT will be in K column. If any formula can read and transform 2 type of data source.





Sorry my English

thanks




TSB BARCODE 2023 - Life Span.xlsxJK5DATE CODEDATE CONVERT388L221106000266/11/20223891D20221130#VALUE!NEW Data TSB SCANNERCell FormulasRangeFormulaK388:K389K388=DATEVALUE(TEXTJOIN("/",,(MID(J388,{6,4,2},2))))*OR(DATEVALUE(TEXTJOIN("/",,(MID(J388,{10,4,2},2)))))Cells with Conditional FormattingCellConditionCell FormatStop If TrueL2,H1,H3:H1048576,J1:L1,I6:I2785,M6:M2785,J3:L1048576Cell Valuecontains "NOT IN DATABASE"textNOL2,H1,H3:H1048576,J1:L1,I6:I2785,M6:M2785,J3:L1048576Cell Valuecontains "NOT IN DATABASE"textNO


----------



## Peter_SSs (Jan 3, 2023)

So, are you saying that these points are all true?

if column J starts with a letter then the date will be the 6 digits after that with just 2 digits for the year, and
if column J starts with a digit then the date will start at the 3rd character in the cell and be 8 digits with a 4-digit year, and
there will be no other formats in column J


----------



## Alex Blakenburg (Jan 4, 2023)

We are looking for patterns in your data , with only 1 example of each it doesn't really allow us to do that.
Another question in addition to Peter's questions is that on the data you gave us one is 12 characters long and one is 10 characters long, is this consistent across the 2 types of data.


----------



## handri (Jan 3, 2023)

Hi 

i failed to change the fromat from 221124 TO 241122 (DD,MM.YY)

Thanks

TSB BARCODE 2023 - Life Span.xlsxJKLM5DATE CODEDATEDATE CONVERTEXP.DATE6L2211240261022112431,05,250530/11/2505NEW Data TSB SCANNERCell FormulasRangeFormulaK6K6=MID(J6,2,6)L6L6=TEXT(K6,"dd,mm,yyyy")M6M6=EDATE(K6,6)Cells with Conditional FormattingCellConditionCell FormatStop If TrueM2,H1,H3:H1048576,J1:M1,I6:I2785,N6:N2785,J3:M1048576Cell Valuecontains "NOT IN DATABASE"textNOM2,H1,H3:H1048576,J1:M1,I6:I2785,N6:N2785,J3:M1048576Cell Valuecontains "NOT IN DATABASE"textNO


----------



## handri (Jan 4, 2023)

Alex Blakenburg said:


> We are looking for patterns in your data , with only 1 example of each it doesn't really allow us to do that.
> Another question in addition to Peter's questions is that on the data you gave us one is 12 characters long and one is 10 characters long, is this consistent across the 2 types of data.


Yes there is two types 12 and 10 characters. Because of these is scan from a barcode.
The barcode date is created with these characters. I  need to exclude the text from the date as needed.

thanks


----------



## handri (Jan 4, 2023)

Peter_SSs said:


> So, are you saying that these points are all true?
> 
> if column J starts with a letter then the date will be the 6 digits after that with just 2 digits for the year, and
> if column J starts with a digit then the date will start at the 3rd character in the cell and be 8 digits with a 4-digit year, and
> there will be no other formats in column J


Hi Pete

correct

thanks


----------



## Alex Blakenburg (Jan 4, 2023)

See if this works for you:

Book1JK5DATE CODEDATE CONVERT6L221106000266/11/202271D2022113030/11/2022Sheet1Cell FormulasRangeFormulaK6:K7K6=IF(LEN(J6)=12,
            DATEVALUE(TEXTJOIN("/",,(MID(J6,{6,4,2},2))))*OR(DATEVALUE(TEXTJOIN("/",,(MID(J6,{10,4,2},2))))),
            DATE(MID(J6,3,4),MID(J6,7,2),RIGHT(J6,2)))


----------



## handri (Jan 4, 2023)

Alex Blakenburg said:


> See if this works for you:
> 
> Book1JK5DATE CODEDATE CONVERT6L221106000266/11/202271D2022113030/11/2022Sheet1Cell FormulasRangeFormulaK6:K7K6=IF(LEN(J6)=12,
> DATEVALUE(TEXTJOIN("/",,(MID(J6,{6,4,2},2))))*OR(DATEVALUE(TEXTJOIN("/",,(MID(J6,{10,4,2},2))))),
> DATE(MID(J6,3,4),MID(J6,7,2),RIGHT(J6,2)))


hi Alex

here comes #valueTSB BARCODE 2023 - Life Span.xlsxJK5DATE CODEDATE CONVERT6L22112402610#VALUE!NEW Data TSB SCANNERCell FormulasRangeFormulaK6K6=IF(LEN(J6)=12,DATEVALUE(TEXTJOIN("/",,(MID(J6,{6,4,2},2))))*OR(DATEVALUE(TEXTJOIN("/",,(MID(J6,{10,4,2},2))))),DATE(MID(J6,3,4),MID(J6,7,2),RIGHT(J6,2)))Cells with Conditional FormattingCellConditionCell FormatStop If TrueL2,H1,H3:H1048576,J1:L1,I6:I2785,M6:M2785,J3:L1048576Cell Valuecontains "NOT IN DATABASE"textNOL2,H1,H3:H1048576,J1:L1,I6:I2785,M6:M2785,J3:L1048576Cell Valuecontains "NOT IN DATABASE"textNO


----------



## Alex Blakenburg (Jan 4, 2023)

Something a bit odd going, I can't recollect the OR part in the formula at all.
Can you try this in J6


```
=IF(LEN(J7)=12,
            DATEVALUE(TEXTJOIN("/",,(MID(J7,{6,4,2},2)))),
            DATE(MID(J7,3,4),MID(J7,7,2),RIGHT(J7,2)))
```


----------



## handri (Jan 4, 2023)

Alex Blakenburg said:


> Something a bit odd going, I can't recollect the OR part in the formula at all.
> Can you try this in J6
> 
> 
> ...


hi Alex

this is exactly what i need. thanks for your help along with Pete.

below is consequences of what i do with the data.

thanks a lot...

TSB BARCODE 2023 - Life Span.xlsxJKLMN5DATE CODEDATE CONVERTEXP.DATESEQRemaining Days6L2211240261024/11/202224/5/2023A1404741D2022113030/11/202230/5/2023CI146NEW Data TSB SCANNERCell FormulasRangeFormulaK6,K474K6=IF(LEN(J7)=12,DATEVALUE(TEXTJOIN("/",,(MID(J7,{6,4,2},2)))),DATE(MID(J7,3,4),MID(J7,7,2),RIGHT(J7,2)))L6,L474L6=EDATE(K6,6)N6,N474N6=L6-TODAY()Cells with Conditional FormattingCellConditionCell FormatStop If TrueN6:N4119Cell Value<$O$6textNOM4120:M1048576,M1:M5Cell Valuecontains "NOT IN DATABASE"textNOM4120:M1048576,M1:M5Other TypeIcon setNOL2,H1,H3:H1048576,J1:L1,I6:I2785,M6:M2785,J3:L1048576Cell Valuecontains "NOT IN DATABASE"textNOL2,H1,H3:H1048576,J1:L1,I6:I2785,M6:M2785,J3:L1048576Cell Valuecontains "NOT IN DATABASE"textNO


----------



## Alex Blakenburg (Jan 4, 2023)

Thanks for letting us know. Glad we could help.


----------



## handri (Jan 4, 2023)

Alex Blakenburg said:


> Thanks for letting us know. Glad we could help.


 I cant blow my mind how you make the formula


----------

