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