| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to turn decimal hours into hours and minutes?
i want to display my vacation hours as as hours and minutes. right now lets
say i have 237.83 displayed. i would like to find a way to display this as "237 hours 50 minutes". if i can get with text, that would be great. if not, i can work with that as well. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to turn decimal hours into hours and minutes?
Hi Josh,
Divide the number 237.83 by 24 and use number format-custom and set the format to [h]:mm -- Regards, OssieMac "Josh" wrote: i want to display my vacation hours as as hours and minutes. right now lets say i have 237.83 displayed. i would like to find a way to display this as "237 hours 50 minutes". if i can get with text, that would be great. if not, i can work with that as well. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to turn decimal hours into hours and minutes?
Huh? How does dividing 237.83 hours by 24 convert that into 237 hours and
50 minutes? Tyro "OssieMac" wrote in message ... Hi Josh, Divide the number 237.83 by 24 and use number format-custom and set the format to [h]:mm -- Regards, OssieMac "Josh" wrote: i want to display my vacation hours as as hours and minutes. right now lets say i have 237.83 displayed. i would like to find a way to display this as "237 hours 50 minutes". if i can get with text, that would be great. if not, i can work with that as well. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to turn decimal hours into hours and minutes?
It does it exactly the way OssieMac stated. This is how Excel handles times.
Try it and you will see. Regards, Fred. "Tyro" wrote in message . net... Huh? How does dividing 237.83 hours by 24 convert that into 237 hours and 50 minutes? Tyro "OssieMac" wrote in message ... Hi Josh, Divide the number 237.83 by 24 and use number format-custom and set the format to [h]:mm -- Regards, OssieMac "Josh" wrote: i want to display my vacation hours as as hours and minutes. right now lets say i have 237.83 displayed. i would like to find a way to display this as "237 hours 50 minutes". if i can get with text, that would be great. if not, i can work with that as well. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to turn decimal hours into hours and minutes?
If you were doing it on paper, how would you do it? Then take that and
create a formula in Excel to show the answer. Tyro "Josh" wrote in message ... i want to display my vacation hours as as hours and minutes. right now lets say i have 237.83 displayed. i would like to find a way to display this as "237 hours 50 minutes". if i can get with text, that would be great. if not, i can work with that as well. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to turn decimal hours into hours and minutes?
Hi Tyro,
firstly the OP said "i want to display my vacation hours as as hours and minutes". The method I have given will display theat. The best way is for you to insert the number divided by 24 in a cell in Excel and then use the number format the way I described. Actually 237.83hrs will display 237:49 because 237hrs 50mins is 237.83 with the 3 recurring. Would need to include a few more of the recurring 3's to get 237:50. In Excel, time is actually a fraction of a day. I am sure if you do a search on the internet, you will find an explanation as to how Excel handles dates and times. -- Regards, OssieMac "Tyro" wrote: If you were doing it on paper, how would you do it? Then take that and create a formula in Excel to show the answer. Tyro "Josh" wrote in message ... i want to display my vacation hours as as hours and minutes. right now lets say i have 237.83 displayed. i would like to find a way to display this as "237 hours 50 minutes". if i can get with text, that would be great. if not, i can work with that as well. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to turn decimal hours into hours and minutes?
I already have made a spread sheet that shows the Excel time values for each
second of the day. Tyro "OssieMac" wrote in message ... Hi Tyro, firstly the OP said "i want to display my vacation hours as as hours and minutes". The method I have given will display theat. The best way is for you to insert the number divided by 24 in a cell in Excel and then use the number format the way I described. Actually 237.83hrs will display 237:49 because 237hrs 50mins is 237.83 with the 3 recurring. Would need to include a few more of the recurring 3's to get 237:50. In Excel, time is actually a fraction of a day. I am sure if you do a search on the internet, you will find an explanation as to how Excel handles dates and times. -- Regards, OssieMac "Tyro" wrote: If you were doing it on paper, how would you do it? Then take that and create a formula in Excel to show the answer. Tyro "Josh" wrote in message ... i want to display my vacation hours as as hours and minutes. right now lets say i have 237.83 displayed. i would like to find a way to display this as "237 hours 50 minutes". if i can get with text, that would be great. if not, i can work with that as well. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to turn decimal hours into hours and minutes?
"237 hours 50 minutes". if i can get with text, that would be great.
A1 = decimal value = 237.83 =INT(A1)&" hours "&RIGHT(TEXT(A1/24,"h:mm"),2)&" mins" Returns: 237 hours 49 mins -- Biff Microsoft Excel MVP "Josh" wrote in message ... i want to display my vacation hours as as hours and minutes. right now lets say i have 237.83 displayed. i would like to find a way to display this as "237 hours 50 minutes". if i can get with text, that would be great. if not, i can work with that as well. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to turn decimal hours into hours and minutes?
I think you can do away with the two string function calls by doing this...
=INT(A1)&" hours "&MINUTE(A1/24)&" minutes" Rick "T. Valko" wrote in message ... "237 hours 50 minutes". if i can get with text, that would be great. A1 = decimal value = 237.83 =INT(A1)&" hours "&RIGHT(TEXT(A1/24,"h:mm"),2)&" mins" Returns: 237 hours 49 mins -- Biff Microsoft Excel MVP "Josh" wrote in message ... i want to display my vacation hours as as hours and minutes. right now lets say i have 237.83 displayed. i would like to find a way to display this as "237 hours 50 minutes". if i can get with text, that would be great. if not, i can work with that as well. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to turn decimal hours into hours and minutes?
Although perhaps this would be better...
=INT(A1)&" hour"&IF(INT(A1)=1," ","s ")&MINUTE(A1/24)&" minute"&IF(MINUTE(A1/24)=1,"","s") as it properly handles the "s" (plural/single) for when either/both the hours and minutes are 1. Rick "Rick Rothstein (MVP - VB)" wrote in message ... I think you can do away with the two string function calls by doing this... =INT(A1)&" hours "&MINUTE(A1/24)&" minutes" Rick "T. Valko" wrote in message ... "237 hours 50 minutes". if i can get with text, that would be great. A1 = decimal value = 237.83 =INT(A1)&" hours "&RIGHT(TEXT(A1/24,"h:mm"),2)&" mins" Returns: 237 hours 49 mins -- Biff Microsoft Excel MVP "Josh" wrote in message ... i want to display my vacation hours as as hours and minutes. right now lets say i have 237.83 displayed. i would like to find a way to display this as "237 hours 50 minutes". if i can get with text, that would be great. if not, i can work with that as well. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to turn decimal hours into hours and minutes?
Damn! I forgot about the newsreader breaking lines at spaces. Here is the
same formula broken so that the newreader won't do that... =INT(A1)&" hour"&IF(INT(A1)=1," ","s ")&MINUTE(A1/24)& " minute"&IF(MINUTE(A1/24)=1,"","s") Rick "Rick Rothstein (MVP - VB)" wrote in message ... Although perhaps this would be better... =INT(A1)&" hour"&IF(INT(A1)=1," ","s ")&MINUTE(A1/24)&" minute"&IF(MINUTE(A1/24)=1,"","s") as it properly handles the "s" (plural/single) for when either/both the hours and minutes are 1. Rick "Rick Rothstein (MVP - VB)" wrote in message ... I think you can do away with the two string function calls by doing this... =INT(A1)&" hours "&MINUTE(A1/24)&" minutes" Rick "T. Valko" wrote in message ... "237 hours 50 minutes". if i can get with text, that would be great. A1 = decimal value = 237.83 =INT(A1)&" hours "&RIGHT(TEXT(A1/24,"h:mm"),2)&" mins" Returns: 237 hours 49 mins -- Biff Microsoft Excel MVP "Josh" wrote in message ... i want to display my vacation hours as as hours and minutes. right now lets say i have 237.83 displayed. i would like to find a way to display this as "237 hours 50 minutes". if i can get with text, that would be great. if not, i can work with that as well. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
how to turn decimal hours into hours and minutes?
i put it in this way and changed to cell to match but still got # value!. i
tried all the ways listed but none gave a correct value. i know it is something simple i missed but can't find it. "Rick Rothstein (MVP - VB)" wrote: Damn! I forgot about the newsreader breaking lines at spaces. Here is the same formula broken so that the newreader won't do that... =INT(A1)&" hour"&IF(INT(A1)=1," ","s ")&MINUTE(A1/24)& " minute"&IF(MINUTE(A1/24)=1,"","s") Rick "Rick Rothstein (MVP - VB)" wrote in message ... Although perhaps this would be better... =INT(A1)&" hour"&IF(INT(A1)=1," ","s ")&MINUTE(A1/24)&" minute"&IF(MINUTE(A1/24)=1,"","s") as it properly handles the "s" (plural/single) for when either/both the hours and minutes are 1. Rick "Rick Rothstein (MVP - VB)" wrote in message ... I think you can do away with the two string function calls by doing this... =INT(A1)&" hours "&MINUTE(A1/24)&" minutes" Rick "T. Valko" wrote in message ... "237 hours 50 minutes". if i can get with text, that would be great. A1 = decimal value = 237.83 =INT(A1)&" hours "&RIGHT(TEXT(A1/24,"h:mm"),2)&" mins" Returns: 237 hours 49 mins -- Biff Microsoft Excel MVP "Josh" wrote in message ... i want to display my vacation hours as as hours and minutes. right now lets say i have 237.83 displayed. i would like to find a way to display this as "237 hours 50 minutes". if i can get with text, that would be great. if not, i can work with that as well. |
|
#13
|
|||
|
|||
Thank you, Biff! Would that everyone herein possess such perspicacity!
(If any doubt my sincerity, please bear in mind I created this acct with the sole purpose of expressing my gratitude. Seriously.) Quote:
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do i change hours and minutes into a decimal number? | New Users to Excel | |||
| Converting Hours:Minutes to Decimal | Excel Worksheet Functions | |||
| Convert Decimal hours and Minutes to minutes please. | Excel Worksheet Functions | |||
| Hours & Minutes to Decimal | Excel Discussion (Misc queries) | |||
| how to change a decimal number (minutes) into hours and minutes? | Excel Discussion (Misc queries) | |||
Hybrid Mode
