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

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?

Thanks in advance.
0
isaacr25
Asked:
isaacr25
  • 3
  • 2
  • 2
  • +4
4 Solutions
 
Michael FowlerSolutions 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

Open in new window

0
 
Wilder1626Commented:
Hi, do you have a excel file that you can share?
0
 
Katie PierceCommented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
isaacr25Author Commented:
It's in a single column. I have attached a sample of the xls file.
0
 
Michael FowlerSolutions ConsultantCommented:
@isaacr25 the attachment didn't take.
0
 
isaacr25Author Commented:
0
 
Saqib Husain, SyedEngineerCommented:
Try

=LEFT(B2,FIND(" ",B2)-1)+MID(B2,FIND(",",B2)+2,2)/24+MID(B2,FIND("m",B2)-3,2)/24/60
0
 
Michael FowlerSolutions 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

Open in new window


I have attached your example for the formula in place too
TAT-PreVine-Subtasks---Copy.xls
0
 
Saqib Husain, SyedEngineerCommented:
Michael74, you need to consider more than 100 days
0
 
Glenn RayExcel 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
 
Martin LissOlder 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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now