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

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.