Special weekday formula needed

KentKHI

Active Member
Joined
Oct 1, 2004
Messages
492
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
You could try an array formula like this

=SUM(IF(ISERROR(MATCH(A1:A7,B1:B7,0)),0,1))>0
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Then you will have to define what you mean by being the same week, the rule to determine this.
 
Upvote 0
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)
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.

Any questions about this gobbeldy gook??
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top