+ Reply to Thread
Results 1 to 7 of 7

Convert days in decimal to days:hours:minutes

  1. #1
    Todd F.
    Guest

    Convert days in decimal to days:hours:minutes

    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

  2. #2
    Niek Otten
    Guest

    Re: Convert days in decimal to days:hours:minutes

    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

    --
    Kind regards,

    Niek Otten

    "Todd F." <[email protected]> wrote in message news:[email protected]...
    >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




  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Quote Originally Posted by Niek Otten
    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

    --
    Kind regards,

    Niek Otten

    "Todd F." <[email protected]> wrote in message news:[email protected]...
    >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
    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 formula in another cell

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

    MROUND is part of analysis toolpak

  4. #4
    Todd F.
    Guest

    beyond 30 days is problem

    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






    "Niek Otten" wrote:

    > 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
    >
    > --
    > Kind regards,
    >
    > Niek Otten
    >
    > "Todd F." <[email protected]> wrote in message news:[email protected]...
    > >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

    >
    >
    >


  5. #5
    Todd F.
    Guest

    yippy it works - thank you very much

    outstanding thankyou very much this is a cool formula

    "daddylonglegs" wrote:

    >
    > Niek Otten Wrote:
    > > 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
    > >
    > > --
    > > Kind regards,
    > >
    > > Niek Otten
    > >
    > > "Todd F." <[email protected]> wrote in message
    > > news:[email protected]...
    > > >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

    >
    > 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 formula
    > in another cell
    >
    > =INT(MROUND(A1,"00:01"))&":"&TEXT(MROUND(A1,"00:01"),"hh:mm")
    >
    > MROUND is part of analysis toolpak
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=523174
    >
    >


  6. #6
    Sloth
    Guest

    RE: beyond 30 days is problem

    =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.

    "Todd F." wrote:

    > 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
    >
    >
    >
    >
    >
    >
    > "Niek Otten" wrote:
    >
    > > 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
    > >
    > > --
    > > Kind regards,
    > >
    > > Niek Otten
    > >
    > > "Todd F." <[email protected]> wrote in message news:[email protected]...
    > > >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

    > >
    > >
    > >


  7. #7
    Todd F.
    Guest

    RE: beyond 30 days is problem

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

    "Sloth" wrote:

    > =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.
    >
    > "Todd F." wrote:
    >
    > > 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
    > >
    > >
    > >
    > >
    > >
    > >
    > > "Niek Otten" wrote:
    > >
    > > > 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
    > > >
    > > > --
    > > > Kind regards,
    > > >
    > > > Niek Otten
    > > >
    > > > "Todd F." <[email protected]> wrote in message news:[email protected]...
    > > > >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 Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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