Coming up with a Unique Number / Identifier Every Time: Besides Actual Full Date & Time

Update 1:

This is part in search of a practical solution and more in part exploration.

Let me phrase the question another way: If you were to look at an item, and be told to give it a number, and to be sure that number was not used previously, with previously not necessarily meaning never, but not used in the last 1,000 to 2,000 occurrences, and to not look at any list to be sure the number wasn't used previously, other than date and time, what would you do? If there are literally one or two duplicates, that would be fine.

Update 2:

Assuming that in most cases the person has access to either the internet or Excel, I suppose I can always use a random number generator. I think I can do a number between 1 and 9,999, as I wouldn't want more than 4 digits. What I am curious about but am not sure how to calculate are the following: 1) Assuming I want only 1,000 numbers, what's the probability of me getting a duplicate number? 2) Assuming I want only 2,000 numbers, what's the probability of me getting a duplicate number?

ORIGINAL POST:

I'm labeling some items by numbers, and would like to quickly assign an identifier / numbers to those things, without having to always check a list to see if the number was already used previously.

One way of course to do so is to simply label those items with the current date and time, as there would never be a duplicate. I however don't want to do so, plus the labeling / hand writing takes too long, especially when I am labeling the items on multiple sides.

Idea 1:
One idea I have, but don't have the time to test it out in Excel is to add the numbers up for the current date and time. So, for example, for 11/22/19 and 7:41 a.m. I would do 11+22+19+7+41 and if it were p.m., I would simply use the 24 hour format, so 19, instead of 7.

1. However, will this method result in duplicates? If yes, when -- within hours, days, months, or years?

2. I don't fully like this method as it will take some time to constantly be adding up the numbers.

Idea 2:
What about adding up the left digit of the minutes to the hour and then writing the seconds? So, 7:47 25 seconds = 1125
How long before duplicates result though? Keep in mind this won't be occurring second by second and there may be anywhere from 30 seconds to perhaps 10 minutes before another label is assigned.

Other Ways?
What other ways are there to pick random, unique numbers?

Here is some additional info:

1. The numbers don't have to be in order. So, 1311, 999, 2005, 275, etc. is fine.

2. As for the limit / max, I suppose 1,000 numbers / identifiers should be more than good enough, but if not, definitely 2,000.

I look forward to the responses.

Thanks.

concepts with adding digits or numbers being part of date time will generate duplicates very soon. for instance for your first case 1/22/19 and 7:41 a.m. I would do 11+22+19+7+41 first duplicate will appear in 59 minutes at 1/22/19 and 8:40 a.m. 11+22+19+8+40 = 100 too


As for the time and date: if you dont ming having date and time separators (depending on setup of your system it could be / . - for date and almost always : for time) you can press two shortcuts for writing date and time:
< Ctrl >+< ; > and < Crtl >+< : >
(control with semicolon then control with colon
and what you will get in the cell would look like (for instance)
2019-11-2214:41

next idea < Ctrl >+< ; > space < Crtl >+< : >
you get regular date and time. Now change format of the cell to General and you will see unique number like
43791,6145833333

LAst one - a macro assigned to a shortcut which will look for a maximum value (lets say in a column with active cell and write in this cell value which is bigger by 1 than current maximum

Two different methods to assign a unique PO number.

The numbers 2250 etc are just a test, you didn't supply a template. The missing number tested the IF Blank cell part.

You can put the start number in any cell and use any starting number you like, just update the formulas to reflect the new start cell, and yes it adds 1 to the last number used.

Ignore the 2nd example, that was made for someone that required an annual start number in different yearly tabs.

Update 1: I've given you the practical solution. If you want a magic formula you could post the question in the VBA forum.

Update 2: Excel Randbetween {1 and 9,999} is a volatile formula that generates unknown amounts of duplicates and changes numbers every single time you change an entry in your spreadsheet, which will require copy and pasting the assigned values over top of the formulas negating using a formula in the first place.

Random number generator in range 1-9999 wil be probably by far unnaceptable for you.

Already between 1000 randomly generated values from 4digits range you can expect usually typically between 40 and 50 duplicates per case and between 1 and 2 "triplicates".

Not trying to calculate it from probability formulas, I did in excel 1000 test generating 1000 numbers from this range.
The lowest number of duplicates (not counting higher repetitions - the highest ever met in the 1000 tests I did was "pentaplicate") received was 27 the highest 65.

So definetely no.

As for ideas for time:
0 = seconds between 0 and 14
15 =seconds between 15 and 29
30 =seconds between 30 and 44
45 =seconds between 30 and 44
+ month (1-12)
these are 2 digits
then day 1-31
hour 24h format
and probably one digit for year
you have 7 digits unique number for any moment not closer to other than 15 seconds
What's more - not clearly connected with date for third party, and rather easy to code. the only calculation is adding month to seconds rounded down to nearest 15.

1) of course seconds 45-59 - 45
2) Missed minutes digits 8&9.
But on the other hand - not in terms of number, but in terms of time: when duplicates would be not a problem - after a year? May be you don't need a year information? It's not that the numbers will be repeated, but that some could repeat.
If you have a calendar handy with "day in a year" number day+ month will be 3 digits 1-365/6 and you could add 500 for PM hours. Then seconds + hours same way I proposed originaly for seconds and month then minutes so in total 7 digits

May be the best way is to have simple mechanical clicker counter. Before each package you click it. The number advances by one, and you use it. No waiting for excel to start, no calculations, power failure proof ...