Date Format in concatenated cell as text

[NicoV]

Hi,

I Received an excel file from a colleague of south Africa, witch is working great, But I would like to change the formula a little bit, because in Belgium we use another date format, and that gives now a issue.

(In Belgium we note dates by default as dd/mm/yyyy)

If my regional settings are set as English (United States) it results the field value as i.e.: MW01-WG-20200823-1-3-N
If my regional settings are set as Dutch (Belgium) it results the field value as i.e.: MW01-WG-yyyy0823-1-3-N

The Issue I have is in this part of the formula: (It takes the value from a cell that is set as with date-format, and it should return that date as a text (set as yyyymmdd)

TEXT(INDIRECT(CONCATENATE("'Field data'!$";$A3;"$8"));"yyyymmdd")

Attached 2 screenshots to illustrate

the complete formula in the field has been given like This :

=IF(INDIRECT(CONCATENATE("'Field data'!$";$A3;"$7"))="";"";CONCATENATE(INDIRECT(CONCATENATE("'Field data'!$";$A3;"$7"));"-";'Field data'!$B$6;"-";TEXT(INDIRECT(CONCATENATE("'Field data'!$";$A3;"$8"));"yyyymmdd");"-";INDIRECT(CONCATENATE("'Field data'!$";$A3;"$9"));"-";;INDIRECT(CONCATENATE("'Field data'!$";$A3;"$10"));"-N"))

If some could help me out to solve this issue, I would be very greatfull.

Thanks in advance

Cheers,

Nico

 

[maabadi]

Text converted all data to text. for converting it to date if cell is E2 Then

=DATE(LEFT(E2,4),MID(E2,5,2),RIGHT(E2,2))

 

[Toadstool]

Hi NicoV,

Don't you need JJJJ instead of YYYY ?

 

[NicoV]

Hi NicoV,

Don't you need JJJJ instead of YYYY ?

Hi Toadstool,
Thanks a lot, So stupid I didn't saw that.

Enjoy your day.

Nico V.

 

[NicoV]

Text converted all data to text. for converting it to date if cell is E2 Then

=DATE(LEFT(E2,4),MID(E2,5,2),RIGHT(E2,2))

Hi Maabadi,
Thanks for your suggestion... I used the tip of Toadstool that worked for me

Enjoy your day

Nico V.