Creating Student IDs from student information

Josiah670

New Member
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.

Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

VBE313

Well-known Member
You need to make that table an actual table, it is using Structured References.

ebea

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

Fluff

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

Replies
3
Views
167
Replies
5
Views
137
Replies
3
Views
293
Replies
0
Views
71
Replies
11
Views
618