What are some random number formulas

140. Excel: Random date or time - a pure matter of opinion

Random date

For the calculation of random data (random date), the star-gazing at the sky found a lot of approaches on the Internet that have one thing in common (at least when creating the date, namely the formula random range (lower_number; upper_number).

Up to this point it's easy because the formula is a typical Excel formula that can be used in conjunction with year (), day (), month (), for example, to calculate random data. But that's where the problem starts, because as is well known, there are months with 28, 29, 30 and 31 days and you simply have to do without these days with normal calculation or have to calculate it with complicated formulas ...

This is of course all nonsense and far too complicated, if you want to determine a valid date by chance that is above 1.1.1900, then you can do this without a complicated formula if you think a little like Excel:

For Excel, a 1 in the date format is 01.01.1900 in the printout, a 2 is 02.01.1900 and a three .... (Can be set in Excel versions from 2007 in the start tab - drop-down menu for cell formatting standard, number, date short or long are displayed here).

For the formula of the random range, z. B. for a random date between 09/15/2011 to 09/15/2013 that the formula z. B. in cell A1:

= Random range (40801; 41532)

In A1's view, the result has to be displayed briefly as a date, because otherwise you would have to be a pretty good mathematician to recognize the randomly generated date.

You can copy the formula from A1 as often as you like using the small handle at the bottom right, so that you can generate as many random dates as you want.

The data that result is always valid and there is always a corresponding date for a number.

How do I get the long numbers out: just enter the regular data in Excel in the spelling dd.mm.yy, enter the view standard or number and that's it.

So z. B.

Enter A1 09/15/2011 and set the view to number
B1 Enter 09/15/2013 and change the view again

In C1 is then the z. B. the formula: = random range (A1; B1). Then set View again as the date for the cell.

If you forget to set the two cells A1 and A2, there is a warning message with the suggestion to correct the formula and you will get the correct numerical value - with the small bonus that the view of the two cells (A1 and B2) does not change, which can be quite useful, since you might want to look at the original data again ... so just press OK and be happy about the result ..

Note: If you want to generate several random numbers in this area using the calculation method mentioned last, then you should work with absolute references:

C1 = RANDOM AREA ($ A $ 2; $ B $ 2)

the content of cells A1 and B1 is as in the example above ...
This generates a new random date in each new line with the small handle at the bottom right of c1. The dollar signs in front of the cell and the number just tell you that the reference cells will remain the same when copied.

The stargazer is astonished, but absolute references are apparently difficult to learn, especially for beginners ...
Yes, of course this also works with = RANDOM AREA (A $ 2; B $ 2), but this should not be a treatise on the use of absolute references ...


Random time

The same applies to the time. The starry sky climber saw interesting solutions here, which are mathematically valid, but rather overwhelm the pragmatic lazy average citizen, as it is relatively easy:

Let's make it a little more universal with three cells

In A1, the lower number stands for the random range, e.g. B. 04:48:00
In B1 the upper number stands for the random range, e.g. B. 05:05:18
and in C1 there is again the formula for the random range or expressed in a valid formula:

= RANDOM NUMBER () * (B1-A1) + A1

and of course the view of the result as time.

Why now with a random number?

Because the random range requires natural numbers. Fractional numbers up to 1 come out of the time: 0.5 is 12:00:00 p.m. Any questions? Check it out for yourself, hours, minutes and seconds are expressed in a fraction of 1 ... The formula above is based on the official formula for restricting random numbers - this can of course also be done with decimal numbers, otherwise Excel is indeed a weak one Arithmetic artist would be ...

The whole thing again with absolute references:

= RANDOM NUMBER () * ($ B $ 1- $ A $ 1) + $ A $ 1


Random time with a random date from 01/01/1900

If you have followed these two steps, you now also know how to cross a random day and time - correctly, with a simple addition in the simplest case:

So we add the result of the random date with the result of the random time: The result is a whole number with places after the decimal point.

We can then look at this number again in the Date view and lo and behold, you have a random day with an indication of the random time.

It is easier if you adapt the random number formula accordingly - many paths lead to the goal - some longer and others shorter ...

You offend the author's intelligence if you need an explanation or an example in formula form for it - :). Otherwise, read the last two chapters and understand ...


Conclusion: If you think a little like Excel, then you no longer have a problem not only in the random data area, but in all invoices with a date from 01/01/1900 onwards. It was not primarily about random numbers for the author, but that the reader should concern himself a little with the calculation method in the background.

In the opinion of the author, on random days and random times, one can doactically convey to a reader how Excel works in the background - this tip could also have been structured very differently- :)

The author is not a rationalist, but an empiricist, which is sometimes an advantage with everyday problems with Excel, if you simply change your view and do not use complicated formulas less expediently, but mathematically superior ...