Reg time, and Overtime

Hello to all in the Kingdom of Excel,

I greatly appreciate this forum, I have learned so much and am continuing to learn every day.

I am trying to create a "time clock" in excel but I am having trouble separating regular time from overtime hours. Let me try to accurately explain.

In column A I have the the weekday with formula:=IF(ISBLANK(B3),"Weekday",IF(DAY(B3)=DAY(C3),TEXT(B3,"ddd"),IF(DAY(C3)>DAY(B3),TEXT(B3,"ddd")&" - "&TEXT(C3,"ddd"),TEXT(B3,"ddd"))))
In column B I have the the start time and date (via macro time stamp) Example: 9/28/2006 3:27:37 PM
In column C I have End time and date (via macro time stamp)
Example: 9/29/2006 12:13:36 AM
In column D I have shift total with this formula: =IF(C3="","00:00",TEXT(C3-B3,"[hh]:mm"))
In Column E I have this formula for a running total for the calendar day: =IF(DAY(B4<>B3),TEXT(D4,"[hh]:mm"),TEXT(SUM(E3+D4),"[hh]:mm"))
In column H i have a straight running total for total hours: =IF(C4>0,TEXT(SUM(H3+E4),"[hh]:mm"),"")

I'm looking for a formula for column F that will only calculate regular time with a week beginning on Sunday and ending Saturday, and likewise a formula for column G that only calculates overtime over 40 hours in a given week.

I hope I have clearly expressed my questions. All help greatly appreciated.

Regards,

Nathan Sargeant

It took me the better part of 5 hours to finally get this. I've attached a sample solution as the formula for the regular hours during the week is "a little" complicated:

=TEXT(IF(IF(WEEKDAY(B3,1)=1,IF(AND(WEEKDAY(B3,1)=7,DAY(B3)<DAY(C3)),DATE(YEAR(C3),MONTH(C3),DAY(C3))-B3,C3-B3+IF(AND(WEEKDAY(B2,1)=7,DAY(B2)<DAY(C2)),C2-DATE(YEAR(C2),MONTH(C2),DAY(C2)))),IF(AND(WEEKDAY(B3,1)=7,DAY(B3)<DAY(C3)),DATE(YEAR(C3),MONTH(C3),DAY(C3))-B3,C3-B3+IF(AND(WEEKDAY(B2,1)=7,DAY(B2)<DAY(C2)),C2-DATE(YEAR(C2),MONTH(C2),DAY(C2))))+F2)>10/6,10/6,IF(WEEKDAY(B3,1)=1,IF(AND(WEEKDAY(B3,1)=7,DAY(B3)<DAY(C3)),DATE(YEAR(C3),MONTH(C3),DAY(C3))-B3,C3-B3+IF(AND(WEEKDAY(B2,1)=7,DAY(B2)<DAY(C2)),C2-DATE(YEAR(C2),MONTH(C2),DAY(C2)))),IF(AND(WEEKDAY(B3,1)=7,DAY(B3)<DAY(C3)),DATE(YEAR(C3),MONTH(C3),DAY(C3))-B3,C3-B3+IF(AND(WEEKDAY(B2,1)=7,DAY(B2)<DAY(C2)),C2-DATE(YEAR(C2),MONTH(C2),DAY(C2))))+F2)),"[hh]:mm")

And the overtime formula is only a little less so.

The two formulas are in the proper columns, but I left E and H empty so you'll have to put your formulas back in them.

Hope this works for you!

- Clay Ver Valen
Excel Help

Clayv,
Thankyou very much for the help! You have been remarkable! This seemed complicated to me, but I never dreamed just how so. You are a star.
Many thanks again,

Nathan

Originally Posted by clayv

It took me the better part of 5 hours to finally get this. I've attached a sample solution as the formula for the regular hours during the week is "a little" complicated:

=TEXT(IF(IF(WEEKDAY(B3,1)=1,IF(AND(WEEKDAY(B3,1)=7,DAY(B3)<DAY(C3)),DATE(YEAR(C3),MONTH(C3),DAY(C3))-B3,C3-B3+IF(AND(WEEKDAY(B2,1)=7,DAY(B2)<DAY(C2)),C2-DATE(YEAR(C2),MONTH(C2),DAY(C2)))),IF(AND(WEEKDAY(B3,1)=7,DAY(B3)<DAY(C3)),DATE(YEAR(C3),MONTH(C3),DAY(C3))-B3,C3-B3+IF(AND(WEEKDAY(B2,1)=7,DAY(B2)<DAY(C2)),C2-DATE(YEAR(C2),MONTH(C2),DAY(C2))))+F2)>10/6,10/6,IF(WEEKDAY(B3,1)=1,IF(AND(WEEKDAY(B3,1)=7,DAY(B3)<DAY(C3)),DATE(YEAR(C3),MONTH(C3),DAY(C3))-B3,C3-B3+IF(AND(WEEKDAY(B2,1)=7,DAY(B2)<DAY(C2)),C2-DATE(YEAR(C2),MONTH(C2),DAY(C2)))),IF(AND(WEEKDAY(B3,1)=7,DAY(B3)<DAY(C3)),DATE(YEAR(C3),MONTH(C3),DAY(C3))-B3,C3-B3+IF(AND(WEEKDAY(B2,1)=7,DAY(B2)<DAY(C2)),C2-DATE(YEAR(C2),MONTH(C2),DAY(C2))))+F2)),"[hh]:mm")

And the overtime formula is only a little less so.

The two formulas are in the proper columns, but I left E and H empty so you'll have to put your formulas back in them.

Hope this works for you!

- Clay Ver Valen
Excel Help