help with functions

[angel]

what Excel function should i use that will output an employee code based on
the hired date?

example:
empcode name date hired
A050401 John Evans - 04/01/05

syntax of empcode is (companycode-yr hired-month hired-number sequence)

 

[Peo Sjoblom]

If you have the company code in A1 (your example A) and the hire date in B1

=A1&TEXT(B1,"yymmdd")


--


Regards,


Peo Sjoblom

 

[T. Valko]

Date of hire in C2

Enter this formula in A2:

="A"&TEXT(C2,"yymm")&TEXT(ROWS(A$2:A2),"00")

Copy down as needed.

 

[Arvi Laanemets]

But what will be the codes, when several employees are hired at once?

It is possible to construct a function which ads an order number for every
employee hired on same day (A050401001, A050401002, etc.), but whenever you
sort your table, those codes are recalculated and the order number part
probably changes. So better use a procedure to calculate a new code and to
write it into table.

I myself use similar codes as key fields for asset databases (written in
Access). The code structure is CCyyyymmddNNN, where CC determines asset
group, yyyymmdd is the registering date for asset, and NNN is registering
order number for this asset in this group and on this day. Whenever a new
row is added, available for user are only a date field with today's date as
default, and an Asset Registering button (userform show only assets from one
selectable group, and any newly registered asset belongs to this group
automatically). Whe the asset is registered, the code will be locked,
registering date control and registering button are hidden, and other fields
are made available.