Intricate Primary Key Generation

[krobbyzw]

Good day all

I have looked for this subject under previous posts, but have not found it, please do direct me to it if I have indeed missed it.

I am in the process of creating concurrent timelines for different countries of dates - BC and AD in Excel. (Column Headings are countries, rows are years).
The project has now grown too big for Excel, so I am in the process of exporting it to Access.
Because the project has to be in date order, I am creating a Primary Key in Excel so it can be viewed in Datasheet View in the database in date order for later export into Access.
I have been manually adding a primary key into the Excel spreadsheet and was wondering if there was a way for Excel to generate it using a formula.
It is in the form BCYYYY_MMDDHHHH where YYYY is the year.
An example is 3760 BC would be BC3760_12311200 as the default for 31 December 3760BC at 1200PM
I would like to know if there is some way to have a formula take BC3760_12311200 and generate BC3759_12311200 on the line below.

Is there any way to automate this process, or must I continue manual entry?

Many thanks for your time.

 

[Andrew Fergus]

Hello and welcome to MrExcel!

Assuming the first date is in cell A1, the following formula in cell A2 will do what you want:
=LEFT(A1,2)&MID(A1,3,LEN(A1)-11)-1&RIGHT(A1,9)

This will only work up to BC1, it will then show BC0. You may want to manually change BC0 to AD0 and then amend the formula such that -1 should be +1.

I trust this helps
Andrew

 

[krobbyzw]

Thank you Andrew!