Creating Student IDs from student information

[Josiah670]

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. Below you can see a sample without the IDs.

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. Below you can see the results of the formula.



P.S. Someone in our financial department suggested the following formula but I could not get it to work at all.


=UPPER(CONCATENATE(LEFT([@[Last Name]],3)&(LEFT([@[First Name]],1))&TEXT([@[Date of Birth]],"ddmmyy")))


Any help would be greatly appreciated. Have a great day.

 

[VBE313]

You need to make that table an actual table, it is using Structured References.

Structured References in Excel

When working with tables in Excel, you can use structured references to make your formulas easier to understand.

www.excel-easy.com

 

[ebea]

And what if they have same day of birth, and names as in two first rows in your example. How to differ those?

 

[Fluff]

It sounds as though some of your dates are text, rather than real dates. You will need to convert them to real dates.