Home |
Search |
Today's Posts |
#1
|
|||
|
|||
calculate difference in time to hours
I am trying to create a spreadsheet to calculate hours worked. I need to know
what formulas to use to calculate the time in hours and overtime hours. for example: A1= 6:00 am start time B1= 11:15 am start lunch C1= 11:45 am end lunch D1= 17:00 pm end time I need E1 to reflect total hours worked to a maximum of 10 hours/day and I need F1 to reflect overtime time hours in excess of 10 hours/day. E1= 10.00 regular hours worked F1= 00.50 overtime hours worked PLEASE HELP! |
#2
|
|||
|
|||
Hi
E1=MIN(10/24,(D1-A1)-(C1-B1)) F1=MAX(0,(D1-A1)-(C1-B1)-10/24) formatted as "hh:mm" or E1=MIN(10,((D1-A1)-(C1-B1))*24) F1=MIN(0,((D1-A1)-(C1-B1))*24-10) formatted as General or Numeric -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Chris" wrote in message ... I am trying to create a spreadsheet to calculate hours worked. I need to know what formulas to use to calculate the time in hours and overtime hours. for example: A1= 6:00 am start time B1= 11:15 am start lunch C1= 11:45 am end lunch D1= 17:00 pm end time I need E1 to reflect total hours worked to a maximum of 10 hours/day and I need F1 to reflect overtime time hours in excess of 10 hours/day. E1= 10.00 regular hours worked F1= 00.50 overtime hours worked PLEASE HELP! |
#3
|
|||
|
|||
E1:
=MIN(10,(D1-A1-(C1-B1))*24) F1: =MAX(0,(D1-A1-(C1-B1))*24-10) HTH Jason Atlanta, GA -----Original Message----- I am trying to create a spreadsheet to calculate hours worked. I need to know what formulas to use to calculate the time in hours and overtime hours. for example: A1= 6:00 am start time B1= 11:15 am start lunch C1= 11:45 am end lunch D1= 17:00 pm end time I need E1 to reflect total hours worked to a maximum of 10 hours/day and I need F1 to reflect overtime time hours in excess of 10 hours/day. E1= 10.00 regular hours worked F1= 00.50 overtime hours worked PLEASE HELP! . |
#4
|
|||
|
|||
Use and IF() function in the two cells like IF((B1-A1)+(D1-C1)=10, 10,
(B1-A1)+(D1-C1)) for cell E1. You can adapt the same function for F1 to show overtime. Is that what you are looking for? "Chris" wrote: I am trying to create a spreadsheet to calculate hours worked. I need to know what formulas to use to calculate the time in hours and overtime hours. for example: A1= 6:00 am start time B1= 11:15 am start lunch C1= 11:45 am end lunch D1= 17:00 pm end time I need E1 to reflect total hours worked to a maximum of 10 hours/day and I need F1 to reflect overtime time hours in excess of 10 hours/day. E1= 10.00 regular hours worked F1= 00.50 overtime hours worked PLEASE HELP! |
#5
|
|||
|
|||
I have a similar question but what I'm trying to achive is in A1 I enter my time in, B1 is my lunch duration entered as 0.5 for a half hour 0.75 for 45 min's ect. and C1 I enter time out and have the total time in E1. I've can get the total time without the lunch but not with it in a decimal format. Any ideas? benb Wrote: Use and IF() function in the two cells like IF((B1-A1)+(D1-C1)=10, 10, (B1-A1)+(D1-C1)) for cell E1. You can adapt the same function for F1 to show overtime. Is that what you are looking for? "Chris" wrote: - I am trying to create a spreadsheet to calculate hours worked. I need to know what formulas to use to calculate the time in hours and overtime hours. for example: A1= 6:00 am start time B1= 11:15 am start lunch C1= 11:45 am end lunch D1= 17:00 pm end time I need E1 to reflect total hours worked to a maximum of 10 hours/day and I need F1 to reflect overtime time hours in excess of 10 hours/day. E1= 10.00 regular hours worked F1= 00.50 overtime hours worked PLEASE HELP!- -- Superslinky |
#6
|
|||
|
|||
Hi
E1=MIN(10/24,(D1-A1)-0.5/24) F1=MAX(0,(D1-A1)-0,5/24-10/24) formatted as "hh:mm" or E1=MIN(10,(D1-A1)*24-0.5) F1=MIN(0,(D1-A1)*24-0.5-10) formatted as General or Numeric When number of regular hours differs from 10, replace this number in both formulas. Arvi Laanemets "Superslinky" wrote in message ... I have a similar question but what I'm trying to achive is in A1 I enter my time in, B1 is my lunch duration entered as 0.5 for a half hour 0.75 for 45 min's ect. and C1 I enter time out and have the total time in E1. I've can get the total time without the lunch but not with it in a decimal format. Any ideas? benb Wrote: Use and IF() function in the two cells like IF((B1-A1)+(D1-C1)=10, 10, (B1-A1)+(D1-C1)) for cell E1. You can adapt the same function for F1 to show overtime. Is that what you are looking for? "Chris" wrote: - I am trying to create a spreadsheet to calculate hours worked. I need to know what formulas to use to calculate the time in hours and overtime hours. for example: A1= 6:00 am start time B1= 11:15 am start lunch C1= 11:45 am end lunch D1= 17:00 pm end time I need E1 to reflect total hours worked to a maximum of 10 hours/day and I need F1 to reflect overtime time hours in excess of 10 hours/day. E1= 10.00 regular hours worked F1= 00.50 overtime hours worked PLEASE HELP!- -- Superslinky |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i calculate the difference in time? | Excel Worksheet Functions | |||
How do I calculate difference in days & hours between two dates e. | Excel Worksheet Functions | |||
calculate negative or positve difference in time | Excel Discussion (Misc queries) | |||
calculate average hours and minutes | Excel Worksheet Functions | |||
calculate average hours worked | Excel Worksheet Functions |