Convert days in decimal to days:hours:minutes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Todd F. - 16 Mar 2006 18:17 GMT

I have a number coming otu in decimal form representing days

.25, .5, 1.25 1.5, 100.5, 368.75, it can be anything up to about 3 years.

I need to convert to Dayss:Hrs:minutes ddd:hh:mm

so 1.5 would be 1:12:00
1.25 would be 1:06:00
368.75 would be 368:18:00

If something is 29 days, 23 hrs, & 50 minutes that is important to my world

Hey thanks for your time

Todd

Reply to this Message

Niek Otten - 16 Mar 2006 18:23 GMT

Hi Todd,

Excel stores dates and times as numbers (1 equals one day) too.
Just format Custom as d:hh:mm and you get waht you require

Signature

Kind regards,

Niek Otten

>I have a number coming otu in decimal form representing days
>
[quoted text clipped - 11 lines]
>
> Todd

Reply to this Message

daddylonglegs - 16 Mar 2006 18:48 GMT

Niek Otten Wrote:

> Hi Todd,
>
[quoted text clipped - 23 lines]
> >
> > Todd

If you format as d:hh:mm you won't get the correct result for 368.75
that format can't show any value above 31:23:59.

I don't think you can achieve this with cell formatting - try a formul
in another cell

=INT(MROUND(A1,"00:01"))&":"&TEXT(MROUND(A1,"00:01"),"hh:mm")

MROUND is part of analysis toolpa

Reply to this Message

daddylonglegs - 16 Mar 2006 18:48 GMT

Niek Otten Wrote:

> Hi Todd,
>
[quoted text clipped - 23 lines]
> >
> > Todd

Reply to this Message

Todd F. - 16 Mar 2006 19:51 GMT

outstanding thankyou very much this is a cool formula

> Niek Otten Wrote:
> > Hi Todd,
[quoted text clipped - 34 lines]
>
> MROUND is part of analysis toolpak

Reply to this Message

Todd F. - 16 Mar 2006 18:59 GMT

I am not getting proper days after 29 - and if I format

dd:hh:mm ddd:hh:mm
395.5 29:12:0 Tue:12:00

any thoughts

> Hi Todd,
>
[quoted text clipped - 16 lines]
> >
> > Todd

Reply to this Message

Sloth - 16 Mar 2006 19:54 GMT

=INT(A1)&":"&TEXT(A1,"hh:mm")

for some reason you can't show elapsed days in Excel. "d" returns the day
of the month (395.5 is equal to January 29th, 1901). You will have to use
the above formula or daddylonglegs' formula. The result of both will be a
text string.

> I am not getting proper days after 29 - and if I format
>
[quoted text clipped - 23 lines]
> > >
> > > Todd

Reply to this Message

Todd F. - 16 Mar 2006 20:17 GMT

I really appreciate the time this formula is goign to really help - thanks
everyone

> =INT(A1)&":"&TEXT(A1,"hh:mm")
>
[quoted text clipped - 30 lines]
> > > >
> > > > Todd

Reply to this Message