How can I make numbers in an Excel cell appear as hours rather than decimals?

ANTONIO HEBO August 14, 2011
Pinterest Stumbleupon Whatsapp

I am running into a trouble to place the time (hours) in a cell that I wanna to display this message:

THE MAINTENANCE TEAM HAS WORKED 23:30 OUT OF 30:00

Instead it appear as decimals. Would you please assist me ?

  1. Tasadak
    August 26, 2011 at 12:57 pm

    try Format cell/Number/All formats and then select h:mm

  2. Bruce Epper
    August 15, 2011 at 10:27 pm

    Since you don't state where you are getting the numbers from in the first place, I'll have to guess.

    If they are coming from another cell in the worksheet, you can use this
    ="THE MAINTENANCE TEAM HAS WORKED " & TEXT(source_cell1,"[h]:mm") & " OUT OF " & TEXT(source_cell2,"[h]:mm")

    This is assuming that both source cells are formatted with one of the Time formats.

    If your times are coming from variables in a macro or VBA code, you just substitute your variable name(s) for the source_cells I used in the formula above.  Again, the variables would have to be declared as Time.

  3. Anonymous
    August 14, 2011 at 10:11 pm

    Do you mean you want a single cell to have both text as well as a time value at the same time? Or are you breaking up that phrase into several cells? If you aren't breaking it up - that's probably what I would do, that way you could right-click and format the cell with the hours in it with a "Date" format.