I'm looking to remove only the trailing zero's from times in excel. i.e. 0700 should display as 07, but 1630 should remain as 1630.

I've been able to do this within a UDF, but it's starting to slow my sheet down. Is there any way of achieving this as an in-built function?

2 Answers

chillin On Best Solutions

Although this question has already been answered, other approaches might include:




In both cases, the return value is a string (the value is no longer internally represented as a number).

SiW77 On

Answer as requested:

=IF(RIGHT(TEXT(A1,"hhmm"),2="00", LEFT(TEXT(A1,"hh"),2), TEXT(A1,"hhmm"))

Have to use the TEXT to identify the time as a string, otherwise excel sees it as a decimal.