TIME formula please help!

[BeHereALot]

I want to calculate work hours, but separate hours over 8 (for overtime, obviously) to be put into a separate cell.
But I also need to account for -30 minutes for break time.

And, the -30 is only accounted for of shifts of 6 hours or more.

so:

8 hour shift = 7.5 hours

10 hour shift = |7.5 hours| -- |2.5 hours ot|

And I'm going to have that calculation running for a weeks' pay period, in this case 14 cells.

So far I can find the difference with

=HOUR(A2-A1)

Thank you so much for the help.

[rahi_feri]

Hi
it's better to send the file! with example on it!
thanks

[BeHereALot]

All projected cells will add the differences (aka the hours worked) with the exception that -30 minutes be deducted from that days' difference (hours worked)
if hours worked that day exceed 6 .

A total (or running total in case of actual) to be calculated and presented for week-end and month-end.

As well as consideration taken that any overtime (hours worked over 8) to be calculated and presented in a separate cell.
Presented for both; week-end and month-end.

[rahi_feri]

Hi
sorry i couldn't understand your question...could you tell an example on the sample file!?
thanks

[BeHereALot]

Ok let me try to ask this again, as I've revised it to probably work more easily now
that the clock in and clock out times have been given their own cell.

I know how to find the difference between the times (aka, hours worked) with the =HOUR() function.

But I'd like the formula to subtract 30 minutes from the hours worked if the hours worked equals 6 or more.

And then I'd like it to calculate into a separate cell anything overtime.



So: =HOUR(C6-C5) =7 (but because it's over 6 i want to subtract 30 minutes) =6.5
So lets assume that for all Projected times.

Now for Actual

Lets say if =HOUR(C13-C12) =10 (then, in two separate cells) RegularTime: =7.5 OverTime: =2


Is that making more sense ?
Thanks for your help, again!


EDIT: I just realized that I can't use =HOUR to find the hours worked and have them added into each other for a "week total" as it won't let me calculate more than 24 'hours'

[rahi_feri]

Hi
Sorry i asked a lot...
Check it
specially the false part!

=HOUR(C6-C5) =7 (but because it's over 6 i want to subtract 30 minutes) =6.5
So lets assume that for all Projected times.

Please Login or Register  to view this content.

[rahi_feri]

Lets say if =HOUR(C13-C12) =10 (then, in two separate cells) RegularTime: =7.5 OverTime: =2

First you said 2.5 and now 2 so decide yourself!
For example
c12=07:00:00
c13=17:00:00
then
In d2:

Please Login or Register  to view this content.

= 7.5
In d3:

Please Login or Register  to view this content.

= 2.5

[rahi_feri]

it won't let me calculate more than 24 'hours'

when the result of summing the time is more then 24 hours use this format

Please Login or Register  to view this content.

and write it in custom format!

[newdoverman]

1 represents 1 day therefore the fraction 1/3 represents 8 hours of 1 day and 1/48 represents 1/2 hour. The results in the following calculations are in hours and minutes formatted as h:mm.

	B	C	D	E
11	Not crossing midnight
12	Start	7:00 AM
13	End	11:00 PM	16:00	hours total
14	Reg Time	7:30	=IF(C13-C12>1/3,1/3-1/48,C13-C12)
15	Overtime	8:00	=IF(C13-C12>1/3,C13-C12-C14-1/48,"")

	G	H	I	J
11		Crossing Midnight
12	Start	7:00 AM
13	End	1:00 AM	18:00	hours total
14	Reg Time	7:30	=IF(H13-H12+(H12>H13)>1/3,1/3-1/48,H13-H12)
15	Overtime	10:00	=IF(H13-H12+(H12>H13)>1/3,H13-H12+(H12>H13)-H14-1/48,"")