Date Format in concatenated cell as text

NicoV

New Member
Joined
Jan 25, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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)
Excel Formula:
TEXT(INDIRECT(CONCATENATE("'Field data'!$";$A3;"$8"));"yyyymmdd")

Attached 2 screenshots to illustrate

the complete formula in the field has been given like This :
Excel Formula:
=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
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    42.1 KB · Views: 2
  • Capture2.JPG
    Capture2.JPG
    41 KB · Views: 2

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,582
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Text converted all data to text. for converting it to date if cell is E2 Then
Excel Formula:
=DATE(LEFT(E2,4),MID(E2,5,2),RIGHT(E2,2))
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,373
Office Version
  1. 2016
Platform
  1. Windows
Hi NicoV,

Don't you need JJJJ instead of YYYY ?
 

NicoV

New Member
Joined
Jan 25, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Text converted all data to text. for converting it to date if cell is E2 Then
Excel Formula:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

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
Top