Creating Student IDs from student information

One of the employees at work asked me for help with an excel issue. She has a large list of students in an excel sheet and she needs to create unique IDs using the first three letters of the last name and and the first letter of the first name and finally the Date of birth in the format ddmmyy. I am not great with excel but tried to help.


After reading some information online, I cam up with this formula:
=UPPER(CONCATENATE(LEFT(D2,3)&(LEFT(C2,1))&TEXT(E2,"ddmmyy")))

There are 2 different issues. All dates starting in single digits 01-09 have the day and month reversed. Also all dates starting from 10 and up will not convert and appear as xx-xx-xxxx, The dates seem to be the main issue and since these excel sheets are large and created by someone else we cannot change them. Am I doing something wrong? Any help would be greatly appreciated.



Date requested ID First name Last Name Date of Birth
16-Mar-20 SMIA070590 Aaron Smith 05-07-1990
16-Mar-20 SMIA26-05-2001 Alex Smith 26-05-2001

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

If I understand correctly then the following should resolve the issue.
1. Populate a helper column (P) for the date using: =IF(--LEFT(E2,1)=0,DATE(RIGHT(E2,4),LEFT(E2,2),MID(E2,4,2)),DATE(RIGHT(E2,4),MID(E2,4,2),LEFT(E2,2)))
Note that the helper column may be moved and/or hidden for aesthetic purposes.
2. Modify the formula in column to read: =UPPER(CONCATENATE(LEFT(D2,3)&(LEFT(C2,1))&TEXT(P2,"ddmmyy")))
Let us know if you have any questions.