+ Reply to Thread
Results 1 to 6 of 6

Time Format with hours and minutes only if needed

  1. #1
    Registered User
    Join Date
    12-21-2012
    Location
    Austin, Texas
    MS-Off Ver
    Microsoft Office 2011 Mac
    Posts
    3

    Time Format with hours and minutes only if needed

    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

  2. #2
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    Excel 2003,2007,2010,2013
    Posts
    1,914

    Re: Time Format with hours and minutes only if needed

    Hi

    Format cells > Custom > hh

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

    Kevin

  3. #3
    Registered User
    Join Date
    12-21-2012
    Location
    Austin, Texas
    MS-Off Ver
    Microsoft Office 2011 Mac
    Posts
    3

    Re: Time Format with hours and minutes only if needed

    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

  4. #4
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    Excel 2003,2007,2010,2013
    Posts
    1,914

    Re: Time Format with hours and minutes only if needed

    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

  5. #5
    Registered User
    Join Date
    12-21-2012
    Location
    Austin, Texas
    MS-Off Ver
    Microsoft Office 2011 Mac
    Posts
    3

    Re: Time Format with hours and minutes only if needed

    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

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,863

    Re: Time Format with hours and minutes only if needed

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1