Suggested revised formula. A while ago I posted a blog post with a suggestion from a reader about how he quickly formatted batches of dates and times. He saw the post and came back with a revised formula – using TimeValue.
He had originally suggested using =(LEFT(G2,1)&”:”&MID(G2,2,2)&”:”&RIGHT(G2,2)) to convert data such as 00514 to a time format as 0:05:14 and that worked really well. However on – mature recollection as they say – he saw a better way. And the formula he suggested was :
Time in hand
=TIMEVALUE(LEFT(G2,1)&”:”&MID(G2,2,2)&”:”&RIGHT(G2,2)) which has an even further elegance to it.
The file with the revised formula is here: date_time_saver_followup
Note that the revised formula is entered into the green cells.