software

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, amp; 50 minutes that is important to my world

Hey thanks for your time

Todd

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

quot;Todd F.quot; gt; wrote in message ...
gt;I have a number coming otu in decimal form representing days
gt;
gt; .25, .5, 1.25 1.5, 100.5, 368.75, it can be anything up to about 3 years.
gt;
gt; I need to convert to Dayss:Hrs:minutes ddd:hh:mm
gt;
gt; so 1.5 would be 1:12:00
gt; 1.25 would be 1:06:00
gt; 368.75 would be 368:18:00
gt;
gt; If something is 29 days, 23 hrs, amp; 50 minutes that is important to my world
gt;
gt; Hey thanks for your time
gt;
gt; Todd

Niek Otten Wrote:
gt; Hi Todd,
gt;
gt; Excel stores dates and times as numbers (1 equals one day) too.
gt; Just format Custom as d:hh:mm and you get waht you require
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;Todd F.quot; gt; wrote in message
gt; ...
gt; gt;I have a number coming otu in decimal form representing days
gt; gt;
gt; gt; .25, .5, 1.25 1.5, 100.5, 368.75, it can be anything up to about 3
gt; years.
gt; gt;
gt; gt; I need to convert to Dayss:Hrs:minutes ddd:hh:mm
gt; gt;
gt; gt; so 1.5 would be 1:12:00
gt; gt; 1.25 would be 1:06:00
gt; gt; 368.75 would be 368:18:00
gt; gt;
gt; gt; If something is 29 days, 23 hrs, amp; 50 minutes that is important to my
gt; world
gt; gt;
gt; gt; Hey thanks for your time
gt; gt;
gt; gt; 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,quot;00:01quot;))amp;quot;:quot;amp;TEXT(MROUND(A1,quot;00:01 quot;),quot;hh:mmquot;)

MROUND is part of analysis toolpak--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=523174
Niek Otten Wrote:
gt; Hi Todd,
gt;
gt; Excel stores dates and times as numbers (1 equals one day) too.
gt; Just format Custom as d:hh:mm and you get waht you require
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;Todd F.quot; gt; wrote in message
gt; ...
gt; gt;I have a number coming otu in decimal form representing days
gt; gt;
gt; gt; .25, .5, 1.25 1.5, 100.5, 368.75, it can be anything up to about 3
gt; years.
gt; gt;
gt; gt; I need to convert to Dayss:Hrs:minutes ddd:hh:mm
gt; gt;
gt; gt; so 1.5 would be 1:12:00
gt; gt; 1.25 would be 1:06:00
gt; gt; 368.75 would be 368:18:00
gt; gt;
gt; gt; If something is 29 days, 23 hrs, amp; 50 minutes that is important to my
gt; world
gt; gt;
gt; gt; Hey thanks for your time
gt; gt;
gt; gt; 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,quot;00:01quot;))amp;quot;:quot;amp;TEXT(MROUND(A1,quot;00:01 quot;),quot;hh:mmquot;)

MROUND is part of analysis toolpak--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=523174I am not getting proper days after 29 - and if I format

dd:hh:mm ddd:hh:mm
395.529:12:0Tue:12:00

any thoughts
quot;Niek Ottenquot; wrote:

gt; Hi Todd,
gt;
gt; Excel stores dates and times as numbers (1 equals one day) too.
gt; Just format Custom as d:hh:mm and you get waht you require
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;Todd F.quot; gt; wrote in message ...
gt; gt;I have a number coming otu in decimal form representing days
gt; gt;
gt; gt; .25, .5, 1.25 1.5, 100.5, 368.75, it can be anything up to about 3 years.
gt; gt;
gt; gt; I need to convert to Dayss:Hrs:minutes ddd:hh:mm
gt; gt;
gt; gt; so 1.5 would be 1:12:00
gt; gt; 1.25 would be 1:06:00
gt; gt; 368.75 would be 368:18:00
gt; gt;
gt; gt; If something is 29 days, 23 hrs, amp; 50 minutes that is important to my world
gt; gt;
gt; gt; Hey thanks for your time
gt; gt;
gt; gt; Todd
gt;
gt;
gt;

outstanding thankyou very much this is a cool formula

quot;daddylonglegsquot; wrote:

gt;
gt; Niek Otten Wrote:
gt; gt; Hi Todd,
gt; gt;
gt; gt; Excel stores dates and times as numbers (1 equals one day) too.
gt; gt; Just format Custom as d:hh:mm and you get waht you require
gt; gt;
gt; gt; --
gt; gt; Kind regards,
gt; gt;
gt; gt; Niek Otten
gt; gt;
gt; gt; quot;Todd F.quot; gt; wrote in message
gt; gt; ...
gt; gt; gt;I have a number coming otu in decimal form representing days
gt; gt; gt;
gt; gt; gt; .25, .5, 1.25 1.5, 100.5, 368.75, it can be anything up to about 3
gt; gt; years.
gt; gt; gt;
gt; gt; gt; I need to convert to Dayss:Hrs:minutes ddd:hh:mm
gt; gt; gt;
gt; gt; gt; so 1.5 would be 1:12:00
gt; gt; gt; 1.25 would be 1:06:00
gt; gt; gt; 368.75 would be 368:18:00
gt; gt; gt;
gt; gt; gt; If something is 29 days, 23 hrs, amp; 50 minutes that is important to my
gt; gt; world
gt; gt; gt;
gt; gt; gt; Hey thanks for your time
gt; gt; gt;
gt; gt; gt; Todd
gt;
gt; If you format as d:hh:mm you won't get the correct result for 368.75.
gt; that format can't show any value above 31:23:59.
gt;
gt; I don't think you can achieve this with cell formatting - try a formula
gt; in another cell
gt;
gt; =INT(MROUND(A1,quot;00:01quot;))amp;quot;:quot;amp;TEXT(MROUND(A1,quot;00:01 quot;),quot;hh:mmquot;)
gt;
gt; MROUND is part of analysis toolpak
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=523174
gt;
gt;

=INT(A1)amp;quot;:quot;amp;TEXT(A1,quot;hh:mmquot;)

for some reason you can't show elapsed days in Excel. quot;dquot; 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.

quot;Todd F.quot; wrote:

gt; I am not getting proper days after 29 - and if I format
gt;
gt; dd:hh:mm ddd:hh:mm
gt; 395.529:12:0Tue:12:00
gt;
gt; any thoughts
gt;
gt;
gt;
gt;
gt;
gt;
gt; quot;Niek Ottenquot; wrote:
gt;
gt; gt; Hi Todd,
gt; gt;
gt; gt; Excel stores dates and times as numbers (1 equals one day) too.
gt; gt; Just format Custom as d:hh:mm and you get waht you require
gt; gt;
gt; gt; --
gt; gt; Kind regards,
gt; gt;
gt; gt; Niek Otten
gt; gt;
gt; gt; quot;Todd F.quot; gt; wrote in message ...
gt; gt; gt;I have a number coming otu in decimal form representing days
gt; gt; gt;
gt; gt; gt; .25, .5, 1.25 1.5, 100.5, 368.75, it can be anything up to about 3 years.
gt; gt; gt;
gt; gt; gt; I need to convert to Dayss:Hrs:minutes ddd:hh:mm
gt; gt; gt;
gt; gt; gt; so 1.5 would be 1:12:00
gt; gt; gt; 1.25 would be 1:06:00
gt; gt; gt; 368.75 would be 368:18:00
gt; gt; gt;
gt; gt; gt; If something is 29 days, 23 hrs, amp; 50 minutes that is important to my world
gt; gt; gt;
gt; gt; gt; Hey thanks for your time
gt; gt; gt;
gt; gt; gt; Todd
gt; gt;
gt; gt;
gt; gt;

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

quot;Slothquot; wrote:

gt; =INT(A1)amp;quot;:quot;amp;TEXT(A1,quot;hh:mmquot;)
gt;
gt; for some reason you can't show elapsed days in Excel. quot;dquot; returns the day
gt; of the month (395.5 is equal to January 29th, 1901). You will have to use
gt; the above formula or daddylonglegs' formula. The result of both will be a
gt; text string.
gt;
gt; quot;Todd F.quot; wrote:
gt;
gt; gt; I am not getting proper days after 29 - and if I format
gt; gt;
gt; gt; dd:hh:mm ddd:hh:mm
gt; gt; 395.529:12:0Tue:12:00
gt; gt;
gt; gt; any thoughts
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Niek Ottenquot; wrote:
gt; gt;
gt; gt; gt; Hi Todd,
gt; gt; gt;
gt; gt; gt; Excel stores dates and times as numbers (1 equals one day) too.
gt; gt; gt; Just format Custom as d:hh:mm and you get waht you require
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Kind regards,
gt; gt; gt;
gt; gt; gt; Niek Otten
gt; gt; gt;
gt; gt; gt; quot;Todd F.quot; gt; wrote in message ...
gt; gt; gt; gt;I have a number coming otu in decimal form representing days
gt; gt; gt; gt;
gt; gt; gt; gt; .25, .5, 1.25 1.5, 100.5, 368.75, it can be anything up to about 3 years.
gt; gt; gt; gt;
gt; gt; gt; gt; I need to convert to Dayss:Hrs:minutes ddd:hh:mm
gt; gt; gt; gt;
gt; gt; gt; gt; so 1.5 would be 1:12:00
gt; gt; gt; gt; 1.25 would be 1:06:00
gt; gt; gt; gt; 368.75 would be 368:18:00
gt; gt; gt; gt;
gt; gt; gt; gt; If something is 29 days, 23 hrs, amp; 50 minutes that is important to my world
gt; gt; gt; gt;
gt; gt; gt; gt; Hey thanks for your time
gt; gt; gt; gt;
gt; gt; gt; gt; Todd
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;