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...
-----------------------------------------------------------------------
"ArturoCohen" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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.



On 21 jun, 13:10, "Earl Kiosterud" <some...@nowhere.com> wrote:
> 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 Kiosterudwww.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...
> -----------------------------------------------------------------------"ArturoCohen" <arturo.co...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > 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

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



--
Regards,

Peo Sjoblom



"ArturoCohen" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
>
>
> On 21 jun, 13:10, "Earl Kiosterud" <some...@nowhere.com> wrote:
>> 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 Kiosterudwww.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...
>> -----------------------------------------------------------------------"ArturoCohen"
>> <arturo.co...@gmail.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>> > 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
>
>

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?

--
howard

"Quis custodiet ipsos custodes?" - Juvenal

"ArturoCohen" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

Super THANKS Peo!

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

Grateful ADC



On 21 jun, 14:31, "Peo Sjoblom" <terr...@mvps.org> wrote:
> 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
>
> --
> Regards,
>
> Peo Sjoblom
>
> "ArturoCohen" <arturo.co...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > 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.
>
> > On 21 jun, 13:10, "Earl Kiosterud" <some...@nowhere.com> wrote:
> >> 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 Kiosterudwww.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...
> >> -----------------------------------------------------------------------"ArturoCohen"
> >> <arturo.co...@gmail.com> wrote in message
>
> >>news:(E-Mail Removed)...
>
> >> > 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

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


--
Regards,

Peo Sjoblom


"Howard Cross" <no-(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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?
>
> --
> howard
>
> "Quis custodiet ipsos custodes?" - Juvenal
>
> "ArturoCohen" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>
>

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



--
Regards,

Peo Sjoblom



"ArturoCohen" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Super THANKS Peo!
>
> it works very good, now im trying to understand how it works, thanks
> for the help.
>
> Grateful ADC
>
>
>
> On 21 jun, 14:31, "Peo Sjoblom" <terr...@mvps.org> wrote:
>> 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
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>> "ArturoCohen" <arturo.co...@gmail.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>> > 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.
>>
>> > On 21 jun, 13:10, "Earl Kiosterud" <some...@nowhere.com> wrote:
>> >> 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 Kiosterudwww.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...
>> >> -----------------------------------------------------------------------"ArturoCohen"
>> >> <arturo.co...@gmail.com> wrote in message
>>
>> >>news:(E-Mail Removed)...
>>
>> >> > 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
>
>

Thanks for pointing that out, I went back and read the first paragraph of
the help file for the function and there it was!

--
howard

"Quis custodiet ipsos custodes?" - Juvenal

"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "Howard Cross" <no-(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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?
>>
>> --
>> howard
>>
>> "Quis custodiet ipsos custodes?" - Juvenal
>>
>> "ArturoCohen" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>>
>>
>
>

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

On Jun 21, 4:08 pm, "Howard Cross" <no-s...@No-Spam.com> wrote:
> Thanks for pointing that out, I went back and read the first paragraph of
> the help file for the function and there it was!
>
> --
> howard
>
> "Quis custodiet ipsos custodes?" - Juvenal
>
> "Peo Sjoblom" <terr...@mvps.org> wrote in message
>
> news:(E-Mail Removed)...
>
> > 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
>
> > --
> > Regards,
>
> > Peo Sjoblom
>
> > "Howard Cross" <no-s...@No-Spam.com> wrote in message
> >news:%(E-Mail Removed)...
> >> 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?
>
> >> --
> >> howard
>
> >> "Quis custodiet ipsos custodes?" - Juvenal
>
> >> "ArturoCohen" <arturo.co...@gmail.com> wrote in message
> >>news:(E-Mail Removed)...
> >>> 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