Time Format with hours and minutes only if needed

**Andy099** · 12-21-2012, 11:53 AM

Good morning,

I have a problem: I am creating an employee schedule right now and in order to save space on the sheet, I would like to create a time format that displays hours (h

and minutes only if needed.

That means if the time is 19:00 only 19 (i.e. without the : and the minute digits) should be displayed and if it is 19:30 it should normally show 19:30.

I would be highly thankful for your help!!!

Thank you,

Andy

**Kevin UK** · 12-21-2012, 11:55 AM

Hi

Format cells > Custom > hh

I have just read your post again, what do you mean by the second part 19:30!

Kevin

**Andy099** · 12-21-2012, 01:36 PM

Thank you for your quick response, Kevin!

With 19:30 I meant that if the time is 19:30 the minute digits should show, i.e. it should be displayed as 19:30.

If the input time is 19:00 it should only show 19, so without the minute digits.

In summary that means, if I type in a full hour I only need two digits (hh) and if it is not a full hour it should show the full time (hh:mm).

Do you now understand what I mean?

Thank you,

Andy

**Kevin UK** · 12-21-2012, 01:45 PM

Hi Andy

Afraid I can't help you on that one, you can only have 1 kind of cell format at a time as far as I know, maybe VBA!

Kevin

**Andy099** · 12-21-2012, 05:43 PM

Thank you for trying to help me, Kevin.

Does anybody else have ideas how to solve my problem?

I would be very thankful if you could help me!

Thank you,

Andreas

**MrShorty** · 12-21-2012, 07:12 PM

Here are your available number format codes http://office.microsoft.com/en-us/ex...005198679.aspx I don't see any format code that will readily allow for "optional" minutes with hours like you are requesting.

Of the top of my head, I can't see any way to do this with formatting alone. One solution would be to make the cell return a text string rather than a date/time serial. If in a helper cell, you used a formula like =if(minute(resultcell)=0,"hh","hh:mm") which will choose the number format that is appropriate to the situation, then use something like =TEXT(resultcell,ifresult), you could get a text string with the desired result.

However, you state that your reason for doing this is to save three characters of text for certain entries. It seems possible to use more space to eliminate these three characters than just showing them.