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


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:


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?

  • 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
    Commented Jun 10, 2020 at 4:42
  • Removed it @teylyn Commented Jun 10, 2020 at 15:43

2 Answers 2


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.


I know this was sorted out already, but here what I came up with. I had a bunch of emails that needed to be formed by concatenating with the domain names. All mail codes are three digits based and some of them had leading zero, like this [email protected] I had the following expression that works for me:


Hope this will help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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