how to turn decimal hours into hours and minutes?

[Josh]

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.

 

[OssieMac]

Hi Josh,

Divide the number 237.83 by 24 and use number format->custom and set the
format to [h]:mm

 

[Tyro]

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

 

[Tyro]

Huh? How does dividing 237.83 hours by 24 convert that into 237 hours and
50 minutes?

Tyro

Hi Josh,

Divide the number 237.83 by 24 and use number format->custom and set the
format to [h]:mm
--
Regards,

OssieMac

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.

 

[OssieMac]

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.

 

[T. Valko]

"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

 

[Rick Rothstein \(MVP - VB\)]

I think you can do away with the two string function calls by doing this...

=INT(A1)&" hours "&MINUTE(A1/24)&" minutes"

Rick

 

[Rick Rothstein \(MVP - VB\)]

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\)]

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

 

[Fred Smith]

It does it exactly the way OssieMac stated. This is how Excel handles times.
Try it and you will see.

Regards,
Fred.

Huh? How does dividing 237.83 hours by 24 convert that into 237 hours and
50 minutes?

Tyro

Hi Josh,

Divide the number 237.83 by 24 and use number format->custom and set the
format to [h]:mm
--
Regards,

OssieMac

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.

 

[Tyro]

I already have made a spread sheet that shows the Excel time values for each
second of the day.

Tyro

 

[Josh]

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\)]

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]

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")

 

[Josh]

i can send the file if that would help

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

 

[Fred Smith]

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]

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.

 

[Rick Rothstein \(MVP - VB\)]

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]

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.