Basic Time Card

**fe007** · 09-15-2012, 03:30 PM

Hi, I'm having trouble with excel formulas. Monday-Friday regular working hours is 8, Saturday's regular hours is 5. But I need help with adding Saturday to the formulas. Formula should be placed in I6 to I36 and J6 to J36. Formula should use IF statement to determine if day is Saturday. Please see the attachment.

**Pete_UK** · 09-15-2012, 03:43 PM

Presumably on Sunday it's all overtime?

Pete

**fe007** · 09-15-2012, 03:50 PM

Yes but right now I'm just interested in getting the Saturday working hours added to the formulas

**Ace_XL** · 09-15-2012, 03:54 PM

Try

In I6
=MIN(((D6-C6)+(F6-E6)+(H6-G6))*24,IF(WEEKDAY(B6)=7,5,8))

In J6
=MAX(((D6-C6)+(F6-E6)+(H6-G6))*24,IF(WEEKDAY(B6)=7,5,8))-IF(WEEKDAY(B6)=7,5,8)

**MarvinP** · 09-15-2012, 03:55 PM

Hi fe007 and welcome to the forum,

Try this formula in your I6 and pull it down:
=IF(((D6-C6)+(F6-E6)+(H6-G6))*24>IF(WEEKDAY(A6)=7,5,8),IF(WEEKDAY(A6)=7,5,8),((D6-C6)+(F6-E6)+(H6-G6))*24)

It looks like Ace_XL and I are on the same wavelenght in using WeekDay()

**Pete_UK** · 09-15-2012, 03:58 PM

Well, this should do both. Put this in I6:

=IF(WEEKDAY(A6,2)=7,0,MIN(IF(WEEKDAY(A6,2)=6,5,8),((D6-C6)+(F6-E6)+(H6-G6))*24))

and this one in J6:

=MAX(((D6-C6)+(F6-E6)+(H6-G6))*24-IF(WEEKDAY(A6,2)=7,0,IF(WEEKDAY(A6,2)=6,5,8)),0)

then copy down.

I basically changed your first formula from this:

=IF(((D6-C6)+(F6-E6)+(H6-G6))*24>8,8,((D6-C6)+(F6-E6)+(H6-G6))*24)

to this:

=MIN(8,((D6-C6)+(F6-E6)+(H6-G6))*24))

and then added the extras, so if you can follow what the MIN function is doing the rest should be straightforward.

Hope this helps.

Pete

**fe007** · 09-15-2012, 04:07 PM

Thanks Ace and Marvin

both worked although Ace's formulas helped for my regular hours and overtime.

**fe007** · 09-15-2012, 04:10 PM

Petes formulas helped as well

**Cutter** · 09-15-2012, 05:48 PM

@ fe007

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.