# format issue

1. ## format issue

I am having cell formatting issues.
first lets do the math. I have 77 items and it takes 2.7 minutes to move one of them from point a to point b.
Doing the math it will take 207.90 total minutes to move all 77 pieces. 77 is in cell A1 and 2.7 is in Cell A2.
The formula I am using is
``Please Login or Register  to view this content.``
. The result is 207.90 in cell A3. What I need is to show Days:hours:minutes in cell A3 so that the result is 00:03:27. o days:3 hours: 27 minutes. I have tried custom formatting with dd:hh:mm and it does not give me the result that I need. Can't figure out what I am doing wrong.

2. ## Re: format issue

what you need to understand about dates/times in Excel is that they are stored as day.fraction of day (recommend this if you are unfamiliar with how Excel stores dates/times: http://www.cpearson.com/Excel/datetime.htm ). All you should need to do is convert your 2.7 minutes into a fraction of a day 2.7/24/60. So something like =A1*A2/24/60 formatted as dd:hh:mm should work. The only caveat I would add is that this is not a true elapsed days format, so if your result will ever be greater than about 31 days (the number of days in January), the format will fail to display correctly. As long as your result will always be less than 31 days, then this should work just fine.

3. ## Re: format issue

Try this:

=(A1*A2)/1440

4. ## Re: format issue

If you want to have the words "days", "hours" and "minutes" in your result, then you will have a text value - you won't be able to use this for further calculations if required.

Hope this helps.

Pete

5. ## Re: format issue

But, just in case you want it like that, you can use this in A3:

=INT((A1*A2)/24/60)&" days "&MOD(INT((A1*A2)/60),24)&" hours "&ROUNDDOWN(MOD(A1*A2,60),0)&" mins"

Hope this helps.

Pete

6. ## Re: format issue

That solved it. Thank you!

7. ## Re: format issue

What solved it?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1