# Special weekday formula needed

#### KentKHI

##### Active Member
I am currently using a timecard that I built that is not working quite right at the change of the month. We calculate overtime on a weekly basis so if a month ends in the middle of the week, the time needs to be calculated towards the next week for overtime sake.

What I am looking for:

I have two seperate ranges with dates in them. I need a formula to return a true value if there are any dates in either range that are within the same week of the dates in the other range.

Does this make any sense?

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### xld

##### Banned
You could try an array formula like this

=SUM(IF(ISERROR(MATCH(A1:A7,B1:B7,0)),0,1))>0

#### KentKHI

##### Active Member
But, my problem is if the days are within the same week then they need to be considered a "match" They do not need to be the same day to be considered a "match"

Am I making sense?

#### barry houdini

##### MrExcel MVP
xld's suggested formula returns TRUE if any date in A1:A7 matches any date in B1:B7, is that what you wanted?

Formula needs to be confirmed with CTRL+SHIFT+ENTER

...or you could use a non-array formula like

=SUMPRODUCT(1-ISNA(MATCH(A1:A7,B1:B7,0)))>0

#### KentKHI

##### Active Member
No, actually, I am looking for a true result if any of the dates are within the same week rather than being the exact same day.

#### xld

##### Banned
Then you will have to define what you mean by being the same week, the rule to determine this.

#### KentKHI

##### Active Member
That is the problem, I am not sure how to define that. By same week, I mean the same calender week (Sunday thru Saturday by the calendar I use and I believe excel does too)

#### KentKHI

##### Active Member
Maybe I need to word this another way?

We pay employees monthly and thier overtime is calculated weekly. Anytime over 40 hours per week is overtime. How do you calculate overtime on a weekly basis when your week is split up by the end of the month?

What I was trying to do is create an if formula that looks at the starting dates of this month and the ending dates of last month to see if they fall within the same week. Is there such a thing as this type of formula? Or are there other ways to go about this?

I really appreciate any help, and thanks for the help so far!

#### Yogi Anand

##### MrExcel MVP
Maybe I need to word this another way?

We pay employees monthly and thier overtime is calculated weekly. Anytime over 40 hours per week is overtime. How do you calculate overtime on a weekly basis when your week is split up by the end of the month?

What I was trying to do is create an if formula that looks at the starting dates of this month and the ending dates of last month to see if they fall within the same week. Is there such a thing as this type of formula? Or are there other ways to go about this?

I really appreciate any help, and thanks for the help so far!

Hi Kent:

I suggest you post a few rows of your sample data which depicts the problem you are facing, the result you are getting now, and your expected result along with any explanation if so needed as to why that is the correct result.

#### KentKHI

##### Active Member
OK, here goes.....

Month of May, Week 5
Denver Slagel.xls
ABCDEFGHIJK
39WEEK5DayJobNameTimeCodeFieldTimeMileageOTDescription
40Mon5/26/2008 Yes0.00Holiday
41Tue5/27/200801-476Doss3.0010.2351Yes0.14
42Tue5/27/2008 3.0080Yes0.14painting,primeplanholder
43Tue5/27/200801-474ProductHouse6.0010.2351Yes0.29
44Wed5/28/200801-476Doss2.0010.2351Yes0.10cleaning
45Wed5/28/2008 7.5080Yes0.36runningerrands
46Thu5/29/2008 1.0080Yes0.05morningmeeting
47Thu5/29/200801-474ProductHouse9.0010.2351Yes0.43baseshoe
48Thu5/29/200801-474ProductHouse4.5010.2351Yes0.21baseshoe,plywoodcount
49Thu5/29/2008 4.5080Yes0.21buildingplansholders
50Thu5/29/2008 1.5080Yes0.07paintingplansholders
51Thu5/29/2008 Yes0.00
52  0.00
53TOTAL42.000242.00
May

Now, Month of June Week 1:
Denver Slagel.xls
ABCDEFGHIJK
7WEEK1DayJobNameTimeCodeFieldTimeMileageOTDescription
8Mon6/2/200801-474ProductHouse9.0010.2352Yes9.00
9Tue6/3/200801-474ProductHouse10.0010.2352Yes10.00
10Wed6/4/2008 2.0080Yes2.00
11Wed6/4/200801-474ProductHouse10.0010.2352Yes10.00
12Thu6/5/2008 1.0080Yes1.00
13Thu6/5/200801-474ProductHouse11.0010.2352Yes11.00
14Fri6/6/200801-474ProductHouse9.5010.2352Yes9.50
15Fri6/6/200801-474ProductHouse3.0010.2352Yes3.00
16  0.00
17TOTAL55.50055.555.50
Jun

oldtime2:
Denver Slagel.xls
ABCD
7542.00
Jun

- oldtime2 = total time for the last week in May (shows only if the week wasn't worked thru Friday)

- The problem I run into is when an employee takes friday off and logs no hours for that day. The formulas are set up so that it assumes that the first week of the next month calculates overtime with the last month. If this is confusing, refer to the next statement.

MY GOAL:

I want to get rid of the "oldtime2" formula and use the following method to calculate overtime...

I want to set up the formulas in column I of June week 1 so that they refer to the range of dates in column B of June Week 1 and compare them to the dates in column B of May Week 5 to make sure the dates are not within the same calendar week. If the dates are in the same week, the formula will calculate using both weeks (May week 5 and June week 1) time to figure overtime. If they are both seperate calendar weeks, it will use only the time of June week 1 to figure overtime.

Replies
3
Views
298
Replies
9
Views
448
Replies
5
Views
284
Replies
15
Views
322
Replies
14
Views
426

1,191,076
Messages
5,984,494
Members
439,893
Latest member
johnsboxftm

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back