hh:mm - mm formated 2 round to .25, .50, .75 & 1
 

I'm using Excel 2003 - I have a text formula (=TEXT(I14-H14,"h.m")) that I'm
using. The times that I have it calculating are 8:00 AM - 5:30 PM which
should be 9.5 hours however it's calculating in 10ths and giving me 9.30
instead. How do I get it formatted to give me 9.50 instead of 9.30?

I'm not a Excel "guru" and have looked up most of my formulas on microsoft
help.

Please help.

Thanks!

Multiply by 24, it is giving you time not 10ths.

--
HTH

Bob Phillips

"Laurie" wrote in message
...
I'm using Excel 2003 - I have a text formula (=TEXT(I14-H14,"h.m")) that
I'm
using. The times that I have it calculating are 8:00 AM - 5:30 PM which
should be 9.5 hours however it's calculating in 10ths and giving me 9.30
instead. How do I get it formatted to give me 9.50 instead of 9.30?

I'm not a Excel "guru" and have looked up most of my formulas on microsoft
help.

Please help.

Thanks!

oh, and format as General.

--
HTH

Bob Phillips

"Laurie" wrote in message
...
I'm using Excel 2003 - I have a text formula (=TEXT(I14-H14,"h.m")) that
I'm
using. The times that I have it calculating are 8:00 AM - 5:30 PM which
should be 9.5 hours however it's calculating in 10ths and giving me 9.30
instead. How do I get it formatted to give me 9.50 instead of 9.30?

I'm not a Excel "guru" and have looked up most of my formulas on microsoft
help.

Please help.

Thanks!

This worked perfectly - Thanks for the information. I am also working on the
same timesheet and I want to summarize Reg Hours and Overtime hours. I need
a formula that will separte reg and overtime hours. For example I have two
cells (2 diff. weeks) that have hours worked for the week. One cell could
have 42 hours in it or it could have 36 hours in it. What I need is a
formula that will pull out anything under 40 and put it in (summary-combined
2 weeks) regular and anything over 40 and put it in (summary-combined 2
weeks) OT.

If more information is needed let me know - I'm not that great at explaining.

Thanks,
Laurie

"Bob Phillips" wrote:

oh, and format as General.

--
HTH

Bob Phillips

"Laurie" wrote in message
...
I'm using Excel 2003 - I have a text formula (=TEXT(I14-H14,"h.m")) that
I'm
using. The times that I have it calculating are 8:00 AM - 5:30 PM which
should be 9.5 hours however it's calculating in 10ths and giving me 9.30
instead. How do I get it formatted to give me 9.50 instead of 9.30?

I'm not a Excel "guru" and have looked up most of my formulas on microsoft
help.

Please help.

Thanks!

=MIN(40,cell_with_hours_in)

gives the regular hours

=MAX(0,cell_with_hours_in-40)

--
HTH

Bob Phillips

"Laurie" wrote in message
...
This worked perfectly - Thanks for the information. I am also working on
the
same timesheet and I want to summarize Reg Hours and Overtime hours. I
need
a formula that will separte reg and overtime hours. For example I have
two
cells (2 diff. weeks) that have hours worked for the week. One cell could
have 42 hours in it or it could have 36 hours in it. What I need is a
formula that will pull out anything under 40 and put it in
(summary-combined
2 weeks) regular and anything over 40 and put it in (summary-combined 2
weeks) OT.

If more information is needed let me know - I'm not that great at
explaining.

Thanks,
Laurie

"Bob Phillips" wrote:

oh, and format as General.

--
HTH

Bob Phillips

"Laurie" wrote in message
...
I'm using Excel 2003 - I have a text formula (=TEXT(I14-H14,"h.m"))
that
I'm
using. The times that I have it calculating are 8:00 AM - 5:30 PM
which
should be 9.5 hours however it's calculating in 10ths and giving me
9.30
instead. How do I get it formatted to give me 9.50 instead of 9.30?

I'm not a Excel "guru" and have looked up most of my formulas on
microsoft
help.

Please help.

Thanks!

Thank you so much. This was exactly what I needed.

Thanks!

"Bob Phillips" wrote:

=MIN(40,cell_with_hours_in)

gives the regular hours

=MAX(0,cell_with_hours_in-40)

--
HTH

Bob Phillips

"Laurie" wrote in message
...
This worked perfectly - Thanks for the information. I am also working on
the
same timesheet and I want to summarize Reg Hours and Overtime hours. I
need
a formula that will separte reg and overtime hours. For example I have
two
cells (2 diff. weeks) that have hours worked for the week. One cell could
have 42 hours in it or it could have 36 hours in it. What I need is a
formula that will pull out anything under 40 and put it in
(summary-combined
2 weeks) regular and anything over 40 and put it in (summary-combined 2
weeks) OT.

If more information is needed let me know - I'm not that great at
explaining.

Thanks,
Laurie

"Bob Phillips" wrote:

oh, and format as General.

--
HTH

Bob Phillips

"Laurie" wrote in message
...
I'm using Excel 2003 - I have a text formula (=TEXT(I14-H14,"h.m"))
that
I'm
using. The times that I have it calculating are 8:00 AM - 5:30 PM
which
should be 9.5 hours however it's calculating in 10ths and giving me
9.30
instead. How do I get it formatted to give me 9.50 instead of 9.30?

I'm not a Excel "guru" and have looked up most of my formulas on
microsoft
help.

Please help.

Thanks!