Excel Query - Converting Exported Number into HH:MM

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

Sparky787

69 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
8332 posts

Uber Geek

Moderator

ID Verified

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.

People often mistake me for an adult because of my age.

Keep calm, and carry on posting.

Referral Links: Sharesies - Backblaze -

Are you happy with what you get from Geekzone? If so, please consider supporting us by subscribing.

No matter where you go, there you are.

Linux

11391 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

69 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

1609 posts

Uber Geek

ID Verified

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

69 posts

Master Geek

#2525940 20-Jul-2020 10:41

Thank you very much - this worked a treat.