Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 103
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,510
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,091
Default 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.



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,091
Default 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.



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,510
Default 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.






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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.



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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.




  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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.





  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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.





  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,389
Default 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.






  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,091
Default 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.






  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 103
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default how to turn decimal hours into hours and minutes?

Click in one of the cells that have your hours in it and select/copy
**everything** in the formula bar (don't retype it for us... select whatever
is in the formula bar and hit Ctrl+C to copy it into the clipboard) and then
paste it onto a separate line in a response to this message.

Rick


"Josh" wrote in message
...
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.







  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 103
Default how to turn decimal hours into hours and minutes?

box s35 shows 237.8300 and contains below

=S34

box s36 contains

=INT(S35)&" hour"&IF(INT(S35)=1," ","s ")&MINUTE(S35/24)&"
minute"&IF(MINUTE(S35/24)=1,"","s")

"Rick Rothstein (MVP - VB)" wrote:

Click in one of the cells that have your hours in it and select/copy
**everything** in the formula bar (don't retype it for us... select whatever
is in the formula bar and hit Ctrl+C to copy it into the clipboard) and then
paste it onto a separate line in a response to this message.

Rick


"Josh" wrote in message
...
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.








  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 103
Default how to turn decimal hours into hours and minutes?

i can send the file if that would help

"Rick Rothstein (MVP - VB)" wrote:

Click in one of the cells that have your hours in it and select/copy
**everything** in the formula bar (don't retype it for us... select whatever
is in the formula bar and hit Ctrl+C to copy it into the clipboard) and then
paste it onto a separate line in a response to this message.

Rick


"Josh" wrote in message
...
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.










  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,389
Default how to turn decimal hours into hours and minutes?

When I copied your formula into Excel, I couldn't get it accepted. I would
always get a formula error around the "minute" part. When I typed it in, it
worked.

My guess is you have some extraneous unprintable characters somewhere in the
formula. I would try retyping it. It worked for me.

Regards,
Fred

"Josh" wrote in message
...
i can send the file if that would help

"Rick Rothstein (MVP - VB)" wrote:

Click in one of the cells that have your hours in it and select/copy
**everything** in the formula bar (don't retype it for us... select
whatever
is in the formula bar and hit Ctrl+C to copy it into the clipboard) and
then
paste it onto a separate line in a response to this message.

Rick


"Josh" wrote in message
...
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.









  #17   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 103
Default how to turn decimal hours into hours and minutes?

i tried retyping it exactly as stated far below with the correct cell but
still get an error. are there supposed to be any spaces in there? i don't
know why this is so hard for me to get.

"Fred Smith" wrote:

When I copied your formula into Excel, I couldn't get it accepted. I would
always get a formula error around the "minute" part. When I typed it in, it
worked.

My guess is you have some extraneous unprintable characters somewhere in the
formula. I would try retyping it. It worked for me.

Regards,
Fred

"Josh" wrote in message
...
i can send the file if that would help

"Rick Rothstein (MVP - VB)" wrote:

Click in one of the cells that have your hours in it and select/copy
**everything** in the formula bar (don't retype it for us... select
whatever
is in the formula bar and hit Ctrl+C to copy it into the clipboard) and
then
paste it onto a separate line in a response to this message.

Rick


"Josh" wrote in message
...
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.










  #18   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default how to turn decimal hours into hours and minutes?

Better would be if you could post it to a web site somewhere so others can
see it also; but if you are not sure how to do that, yes you can send the
file directly to me... just remove the obvious parts from my email address.

Rick


"Josh" wrote in message
...
i can send the file if that would help

"Rick Rothstein (MVP - VB)" wrote:

Click in one of the cells that have your hours in it and select/copy
**everything** in the formula bar (don't retype it for us... select
whatever
is in the formula bar and hit Ctrl+C to copy it into the clipboard) and
then
paste it onto a separate line in a response to this message.

Rick


"Josh" wrote in message
...
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.









  #19   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 103
Default how to turn decimal hours into hours and minutes?

i emailed my file to you. i started a new file and the formula worked. i
don't know why it didn't work in the original file but oh well. thanks for
all your help.

"Rick Rothstein (MVP - VB)" wrote:

Better would be if you could post it to a web site somewhere so others can
see it also; but if you are not sure how to do that, yes you can send the
file directly to me... just remove the obvious parts from my email address.

Rick


"Josh" wrote in message
...
i can send the file if that would help

"Rick Rothstein (MVP - VB)" wrote:

Click in one of the cells that have your hours in it and select/copy
**everything** in the formula bar (don't retype it for us... select
whatever
is in the formula bar and hit Ctrl+C to copy it into the clipboard) and
then
paste it onto a separate line in a response to this message.

Rick


"Josh" wrote in message
...
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.










  #20   Report Post  
Junior Member
 
Location: Nevada
Posts: 1
Thumbs up

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:
Originally Posted by T. Valko View Post
"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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i change hours and minutes into a decimal number? Mills00 New Users to Excel 0 February 25th 08 12:25 PM
Converting Hours:Minutes to Decimal Michael[_2_] Excel Worksheet Functions 2 September 5th 07 04:19 PM
Convert Decimal hours and Minutes to minutes please. Steved Excel Worksheet Functions 13 July 5th 06 05:33 AM
Hours & Minutes to Decimal SAR Excel Discussion (Misc queries) 8 May 8th 06 03:02 PM
how to change a decimal number (minutes) into hours and minutes? Erwin Excel Discussion (Misc queries) 2 November 5th 05 05:22 PM


All times are GMT +1. The time now is 05:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"