Forum Discussion
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
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.
- Jimbo75Copper Contributor
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.
Can you post a recent screenshot where the formula bar doesn't show AM/PM for a time value?