This worked for me for a 5 column Time Sheet (Line 7)... providing that the center time out/in is used for lunch or left blank if no lunch is taken....is there a better array out there?

=IF(H7=I7,((((HOUR(J7)*60)+(MINUTE(J7)))-((HOUR(G7)*60)+(MINUTE(G7)))))/60,((((HOUR(J7)*60)+(MINUTE(J7)))+((HOUR(H7)*60)+( MINUTE(H7)))-((HOUR(I7)*60)+(MINUTE(I7)))-((HOUR(G7)*60)+(MINUTE(G7))))/60))

I used other functions to set dates/times and hour totals. See the attachment for the layout.

Shorter and simpler at the very least <vbg

=((J7-G7)-IF(AND(H7<"",I7<""),I7-H7,0))*24

--
HTH

Bob Phillips

"ChefBoiRD" wrote in message
...

This worked for me for a 5 column Time Sheet (Line 7)... providing that
the center time out/in is used for lunch or left blank if no lunch is
taken....is there a better array out there?


=IF(H7=I7,((((HOUR(J7)*60)+(MINUTE(J7)))-((HOUR(G7)*60)+(MINUTE(G7)))))/60,(
(((HOUR(J7)*60)+(MINUTE(J7)))+((HOUR(H7)*60)+(MINU TE(H7)))-((HOUR(I7)*60)+(M
INUTE(I7)))-((HOUR(G7)*60)+(MINUTE(G7))))/60))

I used other functions to set dates/times and hour totals. See the
attachment for the layout.


+-------------------------------------------------------------------+
|Filename: Timesheet.jpg |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=32|
+-------------------------------------------------------------------+

--
ChefBoiRD

I tried it but excel would only accept it without the quotations after (H7"",I7""). Then, I would get "#NAME?" error...may need the HOUR MINUTE in there somewhere??? Will experiment some more... Thanks!

Something happened in the posting, what is in your message is not what I
posted. I used

=((J7-G7)-IF(AND(H7<"",I7<""),I7-H7,0))*24

note the < between H7 and "" and I7 and ""

Please re-try with this formula.

--
HTH

Bob Phillips

"ChefBoiRD" wrote in message
...

Bob Phillips Wrote:
Shorter and simpler at the very least vbg

=((J7-G7)-IF(AND(H7"",I7""),I7-H7,0))*24

--
HTH

Bob Phillips

snip

I tried it but excel would only accept it without the quotations after
(H7"",I7""). Then, I would get "#NAME?" error...may need the HOUR
MINUTE in there somewhere??? Will experiment some more... Thanks!


--
ChefBoiRD