| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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." wrote in message ... 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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert days in decimal to days:hours:minutes
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." wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Convert days in decimal to days:hours:minutes
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." wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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." wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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." wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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." wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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." wrote in message ... 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert decimal days to Days,hours, minutes | Excel Worksheet Functions | |||
| convert Days to Years, Months, Days | Excel Discussion (Misc queries) | |||
| Convert decimal degree (lattitude/longitude) into Degree, | Excel Discussion (Misc queries) | |||
| Convert # of days to years/days | Excel Discussion (Misc queries) | |||
| convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) | |||
Linear Mode
