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 ...
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)
)
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 20, 2022

Becky_Tom 

If I understood the question correctly, just remove AM/PM from format

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

    SergeiBaklan, thank you, but if I do this it goes to 24 hour clock...ie: 6:30 becomes 18:30

    • 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)
)
      • Becky_Tom's avatar
        Becky_Tom
        Copper Contributor
        Jan 20, 2022
        Thanks, it works!!

Resources