Generated report has an invalid format. Unable to action anything with cell value.

[rikapple]

A piece of software I use generate a report, which I download in Excel (XLSX).

All is ok apart from the time formatting. . The report is broken down over a day in 15-minute increments. I am wanting to look at the time is taken and break it into sections. The issue I have is that the time format is like this 0:00:00.

I am unable to add or move to a pivot table. I have tried a lot of the custom number formats {H}:mm:ss and it feels like every other format. To no avail. Someone suggested the @time fx but unsure how to action this.

Any help would be appreciated as I need to run this report at tomorrow's management meeting.

Many thanks in advance

Claire

Start Time	End Time	Group	Agent	Status	Status Code List	Initiated By	Count	Time
12/6/2021 9:00	12/6/2021 9:15	Tier 1	Fred Smith	WorkingOffline	Status Codes	Agent	1	0:02:33
12/6/2021 9:30	12/6/2021 9:45	Tier 1	Fred Smith	WorkingOffline	Status Codes	Agent	2	0:05:46
12/6/2021 9:45	12/6/2021 10:00	Tier 1	Fred Smith	WorkingOffline	Status Codes	Agent	1	0:02:57
12/6/2021 10:00	12/6/2021 10:15	Tier 1	Fred Smith	WorkingOffline	Status Codes	Agent	1	0:03:56
12/6/2021 10:15	12/6/2021 10:30	Tier 1	Fred Smith	OnBreak	Status Codes	Agent	2	0:09:21
12/6/2021 10:15	12/6/2021 10:30	Tier 1	Fred Smith	WorkingOffline	Status Codes	Agent	2	0:02:19
12/6/2021 10:30	12/6/2021 10:45	Tier 1	Fred Smith	OnBreak	Status Codes	Agent	1	0:00:36
12/6/2021 10:30	12/6/2021 10:45	Tier 1	Fred Smith	WorkingOffline	Status Codes	Agent	1	0:05:25
12/6/2021 10:45	12/6/2021 11:00	Tier 1	Fred Smith	OnBreak	Status Codes	Agent	1	0:08:09
12/6/2021 12:45	12/6/2021 13:00	Tier 1	Fred Smith	WorkingOffline	Status Codes	Agent	1	0:04:19
12/6/2021 13:00	12/6/2021 13:15	Tier 1	Fred Smith	OnBreak	Status Codes	Agent	1	0:00:41
12/6/2021 13:00	12/6/2021 13:15	Tier 1	Fred Smith	OnBreak	Status Codes	Agent	1	0:05:36
12/6/2021 13:00	12/6/2021 13:15	Tier 1	Fred Smith	WorkingOffline	Status Codes	Agent	2	0:08:26
12/6/2021 13:15	12/6/2021 13:30	Tier 1	Fred Smith	OnBreak	Status Codes	Agent	1	0:15:00
12/6/2021 13:30	12/6/2021 13:45	Tier 1	Fred Smith	OnBreak	Status Codes	Agent	2	0:14:40
12/6/2021 13:30	12/6/2021 13:45	Tier 1	Fred Smith	WorkingOffline	Status Codes	Agent	1	0:00:02
12/6/2021 13:45	12/6/2021 14:00	Tier 1	Fred Smith	OnBreak	Status Codes	Agent	1	0:10:52
12/6/2021 13:45	12/6/2021 14:00	Tier 1	Fred Smith	WorkingOffline	Status Codes	Agent	1	0:03:54
12/6/2021 14:00	12/6/2021 14:15	Tier 1	Fred Smith	WorkingOffline	Status Codes	Agent	3	0:10:15
12/6/2021 14:30	12/6/2021 14:45	Tier 1	Fred Smith	OnBreak	Status Codes	Agent	1	0:06:26
12/6/2021 14:30	12/6/2021 14:45	Tier 1	Fred Smith	WorkingOffline	Status Codes	Agent	1	0:04:13
12/6/2021 14:45	12/6/2021 15:00	Tier 1	Fred Smith	OnBreak	Status Codes	Agent	2	0:04:10
12/6/2021 14:45	12/6/2021 15:00	Tier 1	Fred Smith	WorkingOffline	Status Codes	Agent	1	0:07:26
12/6/2021 15:00	12/6/2021 15:15	Tier 1	Fred Smith	OnBreak	Status Codes	Agent	1	0:15:00
12/6/2021 15:15	12/6/2021 15:30	Tier 1	Fred Smith	OnBreak	Status Codes	Agent	1	0:10:17
								0:00:00​

 

[Jeffrey Mahoney]

Make sure to check if the time values are not text cells. My trick is to highlight all the cells, choose 'Text to Columns' from the DATA menu, and click FINISH without selecting anything else. This will convert the text to values.

 

[rikapple]

Jeffrey you are a superstar!!! Thank you

 

[MARK858]

Make sure to check if the time values are not text cells. My trick is to highlight all the cells, choose 'Text to Columns' from the DATA menu, and click FINISH without selecting anything else. This will convert the text to values.

Just a recommendation that you should click Next once before clicking Finish to check that no delimiters have been set (they can affect the result and are one of the options that is remembered from previous use in Text To Columns, in particular the "Other" box).