Timesheet maker

[Lionround]

Excel 2007

I am trying to make a timesheet for our office on which you can enter your time on a daily basis. My problem is that my formula returns the number of hours worked (that sounds like a good problem to most of you); however, if it more than 8 hours, I need it to return 8 in the hours worked column and the remainder in the OT hours column.

My formula thus far in cell E13:

=IF(ISERROR((IF(AND(C13<>0,D13<>0),IF(D13<C13,D13+1-C13,D13-C13),""))*24-0.5),"0",((IF(AND(C13<>0,D13<>0),IF(D13<C13,D13+1-C13,D13-C13),""))*24-0.5))



IF C13 is 8:30 am. D13 is 5:00 pm. I get 8 hours.
IF C13 is 8:30 am. D13 is 7:00 pm. I get 10 hours.

I need to display 8 and add that to the weekly total for regular hours. I need the "extra" 2 hours to be in another cell - in this instance I13.
I know how to get the 2 hours into I-13 (=SUMI13-E13), but I don't know how to get the E13 to display and add the 8.

BTW, the -.05 takes off for lunch without having to "log out and log in".

Hope that makes sense. It is my first post.

 

[Lionround]

It is truncating my formula. I am going to try to post it again.

=IF(ISERROR((IF(AND(C13<>0,D13<>0),IF(D13<C13,D13+1-C13,D13-C13),""))*24-0.5),"0",((IF(AND(C13<>0,D13<>0),IF(D13<C13,D13+1-C13,D13-C13),""))*24-0.5))

 

[asad]

I had the same problem .
Try inserting a space on either side of < or > and it will be posted.

 

[T. Valko]

It is truncating my formula. I am going to try to post it again.


=IF(ISERROR((IF(AND(C13<>0,D13<>0),IF(D13<C13,D13+1-C13,D13-C13),""))*24-0.5),"0",((IF(AND(C13<>0,D13<>0),IF(D13<C13,D13+1-C13,D13-C13),""))*24-0.5))[ p QUOTE]<>

The forum software interprets the < and > characters as html code characters.

When you post a formula that contains these characters put a space on both sides of these characters.

 

[asad]

Try this:

Excel Workbook
	C	D	E	F
13	8:30	17:00	8:00
14	8:30	19:00	8:00	2:00
Sheet1

 

[Lionround]

=IF(ISERROR((IF(AND(C13 < > 0,D13 < > 0),IF(D13 < C13,D13+1-C13,D13-C13),""))*24-0.5),"0",((IF(AND(C13 < > 0,D13 < > 0),IF(D13 < C13,D13+1-C13,D13-C13),""))*24-0.5))

Hopefully the code will post this time. Thanks for the tip on the > being read as html.

 

[T. Valko]

=IF(ISERROR((IF(AND(C13 < > 0,D13 < > 0),IF(D13 < C13,D13+1-C13,D13-C13),""))*24-0.5),"0",((IF(AND(C13 < > 0,D13 < > 0),IF(D13 < C13,D13+1-C13,D13-C13),""))*24-0.5))

Hopefully the code will post this time. Thanks for the tip on the > being read as html.

Can you explain in words what you're wanting to accomplish with that formula? It looks overly complicated! What is the ISERROR doing? Under what condition does that formula generate an error?

What is in C13 and D13? I imagine those are the start and end times?

See if this is what you had in mind...

Book1
	A	B	C	D
1	Start	End	Reg Hrs	OT Hrs
2	10:00 PM	8:00 AM	8	2
Sheet1

Formula in C2 for Regular Hours:

=IF(COUNT(A2:B2)<2,"",MIN(8,MOD(B2-A2,1)*24))

Formula in D2 for Overtime Hours (Hrs >8):

=IF(COUNT(A2:B2)<2,"",MAX(0,MOD(B2-A2,1)*24-8))

 

[Lionround]

I was getting a #VALUE error if there was not data on a given date. That is the purpose for the ISERROR. I needed it to show and add a "0" if someone didn't work that day.

 

[T. Valko]

I was getting a #VALUE error if there was not data on a given date. That is the purpose for the ISERROR. I needed it to show and add a "0" if someone didn't work that day.

OK, in the formulas I suggested repace this "" with 0. Don't put quotes around it! When you quote numbers that changes them from numeric numbers to TEXT numbers. These are not the same!

 

[Lionround]

For overtime hours, I am getting a #VALUE error if there is nothing there. That may be because I added a -.5 to take off for lunch. Otherwise, if they work 8:30 am to 5:00 pm it shows 8 reg hours and .5 OT. It should show 8 reg and 0 OT.