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

1
chillin On Best Solutions

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

=TEXT(A1,IF(MOD(A1*24,1),"hhmm","hh"))

or:

=TEXT(A1,IF(OR(MINUTE(A1),SECOND(A1)),"hhmm","hh"))

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

2
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.