over time in a sheet

i need to put overtime in a work sheet
but here is what i need to do if you work Between 12:00 am to 8:00 am this
is double time now how do i calculate these hours as double time

So what i'm saying is if i come to work a 8:00pm and work unitl 4:00 am
i will invoice the company 4 stright hours and 4 double time hours

i want the program to auto calculate this .

Suppose you have data in ColA and ColB as below...(Format colC and D to time
format)


6/21/09 6:00 PM 6/22/09 8:00 AM 6:00 8:00
6/21/09 8:00 PM 6/21/09 9:00 PM 1:00 0:00

C1(normal hours)
=IF(DAY(A1)=DAY(B1),B1-A1,1-MOD(A1,1))

D1(double hours)
=IF(DAY(A1)=DAY(B1),0,MIN(1/3,MOD(B1,1)))

If this post helps click Yes
---------------
Jacob Skaria


"chilson" wrote:

i need to put overtime in a work sheet
but here is what i need to do if you work Between 12:00 am to 8:00 am this
is double time now how do i calculate these hours as double time

So what i'm saying is if i come to work a 8:00pm and work unitl 4:00 am
i will invoice the company 4 stright hours and 4 double time hours

i want the program to auto calculate this .

You didn't post the layout for your worksheet, so I'll assume you have a
total hours column (say, Column T for this example). Assuming your data
starts in Row 2, use =MIN(T2,4) for your normal hours and =MAX(T2-4,0) for
your overtime hours, so I guess the formula you want would be this...

=MIN(T2,4)+2*MAX(T2-4,0)

--
Rick (MVP - Excel)


"chilson" wrote in message
...
i need to put overtime in a work sheet
but here is what i need to do if you work Between 12:00 am to 8:00 am
this
is double time now how do i calculate these hours as double time

So what i'm saying is if i come to work a 8:00pm and work unitl 4:00 am
i will invoice the company 4 stright hours and 4 double time hours

i want the program to auto calculate this .