Excel Query - Converting Exported Number into HH:MM

Forums › Desktop computing › Excel Query - Converting Exported Number into HH:MM

Sparky787

66 posts

Master Geek

#272847 20-Jul-2020 09:33

Hi Team,

I have an export from LiveChat on Agent Performance and I am trying to get the Accepting Time/Not Accepting time to display in HH:MM

Currently the HH component is displaying correctly, but the minutes have been converted into a number format.

Here is the screenshot of what it looks like in LiveChat:

Here is how it exported into Excel:

Any ideas or suggestions in the first direction is greatly appreciated.

Stu

Hammered
5486 posts

Uber Geek

Moderator

Trusted

Lifetime subscriber

#2525909 20-Jul-2020 09:38

You'll need to edit the required cells (select cells, right click, edit cells) and change to time/date and pick your required format.

Off the top of my head. Not near a computer.

Keep calm, and carry on posting.

Linux

6697 posts

Uber Geek

Trusted

Lifetime subscriber

#2525920 20-Jul-2020 10:03

Stu: You'll need to edit the required cells (select cells, right click, edit cells) and change to time/date and pick your required format.

Off the top of my head. Not near a computer.

Correct and it's very simple

Sparky787

66 posts

Master Geek

#2525924 20-Jul-2020 10:05

Hi - thank you for your quick reply.

The Format feature does not work with Time/Date because it does not have the right formats.

There is a custom format that will allow HH:MM, but when I convert the existing data, I don't get the desired result. I need the data to be a cumulative count of hours and minutes.

When I try the custom format, it does not work either. 77:91 (which should be 77 hours and 51 minutes) converts to 21.##
I've not quite figured out the logic behind it yet.

jamesrt

1002 posts

Uber Geek

Trusted

Lifetime subscriber

#2525933 20-Jul-2020 10:26

Sparky787:

Hi - thank you for your quick reply.

The Format feature does not work with Time/Date because it does not have the right formats.

There is a custom format that will allow HH:MM, but when I convert the existing data, I don't get the desired result. I need the data to be a cumulative count of hours and minutes.

When I try the custom format, it does not work either. 77:91 (which should be 77 hours and 51 minutes) converts to 21.##
I've not quite figured out the logic behind it yet.

You need to convert the numbers to fractions of "24 hours", and then apply a format:

As an example, in a blank Excel spreadsheet, I've just done this in one cell:

=135.99/24

as a formula, which makes the value of the cell "5.66625", then set the cell-fomat to "[h]:mm" (right-click cell, format cells, select "custom", then in the "Type" field, enter "[h]:mm"

Result displays as:

135:59

Sparky787

66 posts

Master Geek

#2525940 20-Jul-2020 10:41

Thank you very much - this worked a treat.