Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Chris
 
Posts: n/a
Default 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   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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   Report Post  
Jason Morin
 
Posts: n/a
Default

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   Report Post  
benb
 
Posts: n/a
Default

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   Report Post  
Superslinky
 
Posts: n/a
Default


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   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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
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 calculate the difference in time? Roze Excel Worksheet Functions 15 September 11th 06 08:36 AM
How do I calculate difference in days & hours between two dates e. probi2 Excel Worksheet Functions 1 January 12th 05 04:59 PM
calculate negative or positve difference in time kpmoore Excel Discussion (Misc queries) 2 January 5th 05 02:35 AM
calculate average hours and minutes llstephens Excel Worksheet Functions 4 November 30th 04 04:47 PM
calculate average hours worked llstephens Excel Worksheet Functions 1 November 24th 04 03:37 PM


All times are GMT +1. The time now is 02:41 PM.

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"