Google-sheets – Preserve leading zeros in a Google Sheets CONCATENATE function

concatenategoogle sheets

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?

Best Answer

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.

Related Topic