Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# Excel conversion from days, hours, minutes

Posted on 2015-01-13
Medium Priority
107 Views
I have a report that has been exported from an application. There are no options to change the output format for the report. It currently outputs time duration in the following format:

36 days, 12 hours, 44 minutes

The column cells are formatted as General. How can I convert these durations to one unit? For instance how can I convert to a total in hours?

0
Question by:isaacr25
• 3
• 2
• 2
• +4

LVL 23

Assisted Solution

Michael Fowler earned 1760 total points
ID: 40548123
If in cell A1 always the same format then something like the following could help you

``````=LEFT(A1, 2) * 24 + MID(A1, 10, 2) + MID(A1, 19, 2)/60
``````
0

LVL 11

Expert Comment

ID: 40548124
Hi, do you have a excel file that you can share?
0

LVL 7

Expert Comment

ID: 40548126
Does it have the days, hours and minutes each in a separate column, or does it just give you "36 days, 12 hours, 44 minutes" in a single column?
0

Author Comment

ID: 40548139
It's in a single column. I have attached a sample of the xls file.
0

LVL 23

Expert Comment

ID: 40548166
@isaacr25 the attachment didn't take.
0

Author Comment

ID: 40548208
0

LVL 43

Assisted Solution

Saqib Husain, Syed earned 240 total points
ID: 40548226
Try

=LEFT(B2,FIND(" ",B2)-1)+MID(B2,FIND(",",B2)+2,2)/24+MID(B2,FIND("m",B2)-3,2)/24/60
0

LVL 23

Accepted Solution

Michael Fowler earned 1760 total points
ID: 40548229
Here is a formula that will do it for you

``````=TRIM(LEFT(B2, 2)) * 24 + TRIM(MID(B2, FIND(",",B2)+2, 2)) + MID(B2,FIND(",",B2,FIND(",",B2,FIND(",",B2)+1))+2,2)/60
``````

I have attached your example for the formula in place too
0

LVL 43

Assisted Solution

Saqib Husain, Syed earned 240 total points
ID: 40548246
Michael74, you need to consider more than 100 days
0

LVL 27

Expert Comment

ID: 40549129
No Points.

Syed's formula works and returns a decimal value if unformatted.  Use [h]:mm as the custom format for these values to show hours and minutes.  The brackets around the "h" allow for hour amounts over 24.

-Glenn
0

LVL 49

Expert Comment

ID: 40596426
I've requested that this question be closed as follows:

Accepted answer: 500 points for Michael74's comment #a40548123

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing howâ€¦
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templaâ€¦
###### Suggested Courses
Course of the Month13 days, 5 hours left to enroll

#### 972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.