Forum Discussion

Becky_Tom's avatar
Becky_Tom
Copper Contributor
Jan 20, 2022
Solved

Format a time cell to a text cell them making it look like a text I want

So I have entries in the time cell, start a end times, I made a text formula and column c is the result (08:30 AM-06:30 PM), what I want it to look like is what is in column D (8:30-6:30), any ideas on how I can accomplish this? Thank you...

formula to make it a text celltime cells

excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 20, 2022

    Becky_Tom 

    That could be something like

    =MID( TEXT(O39, "hh:mm am/pm"),
  IF( 1*LEFT( TEXT(O39, "hh:mm am/pm") ) = 0, 2, 1),
  IF( 1*LEFT( TEXT(O39, "hh:mm am/pm") ) = 0, 4, 5)
) &
 "-" &
 MID( TEXT(P39, "hh:mm am/pm"),
  IF( 1*LEFT( TEXT(P39, "hh:mm am/pm") ) = 0, 2, 1),
  IF( 1*LEFT( TEXT(P39, "hh:mm am/pm") ) = 0, 4, 5)
)

5 Replies

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 20, 2022

        Becky_Tom 

        That could be something like

        =MID( TEXT(O39, "hh:mm am/pm"),
  IF( 1*LEFT( TEXT(O39, "hh:mm am/pm") ) = 0, 2, 1),
  IF( 1*LEFT( TEXT(O39, "hh:mm am/pm") ) = 0, 4, 5)
) &
 "-" &
 MID( TEXT(P39, "hh:mm am/pm"),
  IF( 1*LEFT( TEXT(P39, "hh:mm am/pm") ) = 0, 2, 1),
  IF( 1*LEFT( TEXT(P39, "hh:mm am/pm") ) = 0, 4, 5)
)

Resources