• Status: Solved
• Priority: Medium
• Security: Public
• Views: 113

# Excel conversion from days, hours, minutes

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
isaacr25
• 3
• 2
• 2
• +4
4 Solutions

Solutions ConsultantCommented:
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

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

Commented:
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 Commented:
It's in a single column. I have attached a sample of the xls file.
0

Solutions ConsultantCommented:
@isaacr25 the attachment didn't take.
0

Author Commented:
0

EngineerCommented:
Try

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

Solutions ConsultantCommented:
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

EngineerCommented:
Michael74, you need to consider more than 100 days
0

Excel VBA DeveloperCommented:
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

Older than dirtCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.