| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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! |
|
#2
|
|||
|
|||
|
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! |
|
#3
|
|||
|
|||
|
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! |
|
#4
|
|||
|
|||
|
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! |
|
#5
|
|||
|
|||
|
=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! |
|
#6
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Round a number in nested function | Excel Worksheet Functions | |||
| Round whole numbers up and down | Excel Discussion (Misc queries) | |||
| Round Up to Nearest 10 | Excel Worksheet Functions | |||
| Round time to nearest quarter hr | Excel Worksheet Functions | |||
| How do I ROUND() round off decimals of a column dataset? | Excel Worksheet Functions | |||
Linear Mode
