0

I am trying to create about 50 cells that have number combinations which look like variations of this:

01:05:39

I am using a RANDBETWEEN function to generate three pairs of numbers (e.g. =RANDBETWEEN(0,59) in cells A1, B1, and C1, and then using CONCATENATE (e.g. =CONCATENATE(A1, ":", B1, ":", C1) ) to put them together in another cell. I've tried a number of different methods (including Plain Text formatting on the RANDBETWEEN cells, pasting values only into a different column and using those values, wrapping parts of the formula with a TEXT function, custom formatting with 00:00:00 on the CONCATENATE cells, etc.), but my resulting cells are not retaining the leading zeros. They end up looking like this:

1:5:39

If you're wondering why I'm trying to do this, I'm a product designer and I need to make some realistic looking timestamps for a mockup. Anyone know how to solve this? Maybe I'm overcomplicating things somehow and there's a much easier way?

2
  • Please don't tag with Excel if you don't use Excel. Despite some functionality overlap, there are considerable differences between Sheets and Excel. – teylyn Jun 10 '20 at 4:42
  • Removed it @teylyn – Peter Bentley Jun 10 '20 at 15:43
0

I was overthinking it. I'm able to achieve my goal with this method instead:

  1. Create a cell (A1) with this formula: =RANDBETWEEN(1000,30000)
  2. Create another cell (B1) with this formula: =A1/86400
  3. Set the format for B1 to "Duration". Result reads in a 0:00:00 format.

Thanks to this article and this question/answer for setting me straight.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.