Intricate Primary Key Generation

krobbyzw

New Member
Joined
Nov 1, 2010
Messages
2
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
 

Forum statistics

Threads
1,081,623
Messages
5,360,083
Members
400,570
Latest member
Ben Morgan 1985

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top