Sum by week Formula

[MarkPr]

Hi,

I have been tasked with adding KPI's to my companies attendance tracker.
Currently working on the Pass Outs that have been issued

I currently have the below formula working, it counts the number of days leave which meet certain criteria.

Please Login or Register  to view this content.

The criteria for tbl_Leave[Start Date] & tbl_Leave[End Date] are set at present to use the DATE function with me manually setting the day criteria i.e. 1-7(Week 1), 8-15(Week 2) etc etc.
I can see this becoming an issue in the future when the year changes as the dates will change per week.

I would like to know if there is a way the formula can look at the Start & End date columns on the leave table and determine if the dates meet the week numbers above the yellow boxes on the pass out sheet.

Capture.PNG

I have attached a sample file, some of the pages are locked out but i can unlock additional pages if absolutely neccasary.

Thanks Mark

[CK76]

So your week is Mon to Sun.

But do you use ISO Week numbering or standard Excel week number?

For 2018, Jan 1st fell on Monday so there isn't difference, but for 2017.
=ISOWEEKNUM(Date(2017,1,1))
=WEEKNUM(Date(2017,1,1),2)

Will return different result. For ISOWeeknum = 52, WEEKNUM = 1

That would dictate how you should calculate your date range.

[MarkPr]

Yes Mon to Sun is working week

=WEEKNUM would be the way to go

Thanks

[CK76]

Assuming you have some cell holding year value (I used c13)

For week start.
=MAX(DATE(C13,1,1),DATE(C13,1,1)-WEEKDAY(DATE(C13,1,1),2)+(D13-1)*7+1)

For week end.
=MIN(DATE(C13+1,1,0),DATE(C13,1,1)-WEEKDAY(DATE(C13,1,1),2)+D13*7)

See attached.

[MarkPr]

CK76 that works a treat

I have updated the year value to reference mt calendar year from the settings page and works perfectly.

Many Thanks

[CK76]

You are welcome and thanks for the rep