Excel Query - Converting Exported Number into HH:MM

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

Sparky787

69 posts

Master Geek
+1 received by user: 2

#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.

Sparky

Stu

Hammered
8740 posts

Uber Geek
+1 received by user: 2391

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 -

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

12173 posts

Uber Geek
+1 received by user: 8469

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
+1 received by user: 2

#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.

Sparky

jamesrt

1663 posts

Uber Geek
+1 received by user: 941

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
+1 received by user: 2

#2525940 20-Jul-2020 10:41

Thank you very much - this worked a treat.

Sparky