I have received an Excel report which features a column of cells that contains date (dd/mm/yyyy) and a time code (hh/mm/ss).

However only the time code is required and I cannot, for the life of me, figure out how to format the column in bulk so that only the time code remains. It has stumped colleagues as well.


You can just use the RIGHT() function

=RIGHT(A1,8) will give you just the time code. Then grab the bottom right corner of the cell and drag it down the new column to the end of your data. Then you can format that as time and hide the original column.

  • This would still mean entering a formula and getting it to asign all the columns, which can be a lot of work if you're not experienced with excel. – LPChip Aug 7 '15 at 11:49
  • @LPChip it's one column, as I understand it, so inserting a column to the right and dragging this down will give the desired result. – Raystafarian Aug 7 '15 at 11:50
  • Oh, I know. But your answer does not reflect that, and thats basically my point. You should add in the necessary steps on how to fill all the rows with the same function too. I'm pretty sure if they knew how to do that, they'd figure out how to write the formula themselves too and didn't need to ask here. – LPChip Aug 7 '15 at 11:56
  • Thanks for the edit. I still can't give this a thumbs up because its not clear enough. They'll attempt to drag the entire cell this way, not that black dot in the bottom right corner. Add that, and you'll earn my +1. :) – LPChip Aug 7 '15 at 12:23
  • OK, I tried the RIGHT function on a cell with the following info: "01/03/2015 00:00:30" but it returned the answer 03472222 (not the correct answer of 30 seconds. Any suggestions as to what's gone wrong? – BZ1 Aug 10 '15 at 16:30

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.