count days hours and minutes

  • Thread starter Thread starter ArturoCohen
  • Start date Start date
A

ArturoCohen

Hi all,

I have an array of times in format 00:00 (hh:mm). then, in a separate
cell i have the total sum of all the times,
Eg: 580:23 (hh:mm). I would like to get the results like this: 24
"days" 4 "hours" 23 "minutes"

how can I work around this?

thanks in advance for your help.

ADC
 
Arturo,

Format your cell containing the sum formula (Format - Cells - Number - Custom, Type box):

h "hours" m "minutes" s "seconds"

This will result in "hours" even if it's only one hour. To fix that, you have to get
fancier.

--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
Yes, I did that, but the result i get is 580 hours 23 minutes 00
seconds

i would like it to say 24 days 4 hours 23 minutes (this is because 580
hours = 24 days and 4 hours)

but thanks anyway Earl.
 
One way

=INT(A1)&" Days "&TEXT(A1-INT(A1),"h "&"""hours"""""" m"&""" minutes""")


where A1 holds 580:23

if the result is


0 Days 9 hours 40 minutes



then the values are minutes and seconds and not hours and minutes
 
format special d:h:mm

Would this method (posted above)

=INT(A1)&" Days "&TEXT(A1-INT(A1),"h "&"""hours"""""" m"&""" minutes""")

be better than this method?

=(DAY(A1) &" day " & HOUR(A1) & " hours " & MINUTE(A1) & " minutes")

If so, what is the benefit since it appears as though both yield the same
result?
 
Super THANKS Peo!

it works very good, now im trying to understand how it works, thanks
for the help.

Grateful ADC
 
Because it is incorrect, try it with another number of hours like 855:25
that is 35 days, Day() can never return more than 31 days and if over will
return the difference so it will return 4 days 15 hours and 25 minutes
 
Thanks for the feedback, just a word of advice, unless you use the posted
email address for trash email you shouldn't really post your email address,
you will be spammed since spammers use spambots to collect email addresses
from these newsgroups/webforums
 
Thanks for pointing that out, I went back and read the first paragraph of
the help file for the function and there it was!
 
My thanks to both for the help, I also learned something today! hope I
can help other the way u do too.

thanks 4 all
 
Hi Arturo, I'm still learning and I cruise the NGs for things that I didn't
know. One way for me to learn is ask. I didn't mean to hijack your thread.
 
Arturo,

Then you could use this formatting string:

dd "days" h "hours" m "minutes" s "seconds"

But it would work only up to 31 days.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
 
Hi Howard,

I started to use NGs too to learn this kind of stuff. and is ery
interesting. I didnt understand the hijack thing though.

Earl, thanks, I didn used this way because the time summary will
continue growing for more than 31 days. But thanks anyway for
pointing it.

I'll post a question later for pivot tables, but i still dont figure
it out quite well to ask.

ADC
 
Arturo,

Debra Dalgleish has a bunch of good stuff on pivot tables at www.contextures.com.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
 
Back
Top