Forum Discussion

Jimbo75's avatar
Jimbo75
Copper Contributor
Oct 06, 2022

Excel converting colon separated numbers to time

We use MS Excel for MS365 version 2209.

We have a phone system known as 8x8. We run a report every day counting the outbound calls by our sales team. We only count the calls with a duration longer than ten seconds. 8x8 generates the report and sends it to an XLSX file. We have been running this report every workday for more than a year. There is a column: "Busy Duration" and each cell displays as "h:mm:ss."

Suddenly, this week, Excel started converting the cell to TIME, even though the column has custom formatting to show "[h]:mm:ss." The cell displays the correct format, but the formula bar displays time. 

Example a call duration of one minute and twenty-two seconds displays in the cell as 00:01:22. But the FORMULA BAR says 12:01:22 AM. 

I tried changing the column format to TEXT and get 0.000949074074074074.

I tried making a blank column TEXT and copy/paste the data with no luck.

The only way I have been able to work around is:

Copy the column.

Paste it into NOTEPAD.

Delete the data from the column

Change the format to TEXT.

Copy the data from NOTEPAD

Paste the data back into the column.

This just started happening this week. But we've been running the same report every weekday for more than a year.

Any ideas why this happened, so suddenly?

Any ideas about how to fix it?

Thanks!

8 Replies

  • Jimbo75 

    It looks like your system time format has changed.

    Excel ALWAYS displays a time in the formula bar according to the Long Time format set in your system, regardless of the time format of the cell., just like it always displays a date in the formula bar according to the Short Date format of your system, regardless of the date format of the cell.

    So I suspect that your Long Time format was changed from hh:mm:ss to hh:mm:ss AM/PM.

    • Jimbo75's avatar
      Jimbo75
      Copper Contributor

      HansVogelaar 

      Thank you. I brought that suggestion to our IT manager and he pointed out that change would affect all Excel files. But it only affects files created after yesterday. We have a file from 30 September and the column is displaying correctly. This cell is not supposed to display the time. It's only supposed to display the duration of the call in hours:minutes:seconds.

Resources