HELP WITH EXCEL FORMULA PLEASE

[Kenja824]

This is a formula I dont completely understand but I found it on-line for what I wanted to do and manipulated it to work..... so I thought.

In A18:A388 - I have the dates, running from Jan01 down to Dec31 (this array goes longer than needs to be in case of leap years or any other glitch I might throw in. lol) In column BG, I have the total hours worked for each week that appears only on every Sunday. Our bonuses are not figured from specific dates, rather they are from the first Monday in March, through the first Sunday in September, then another from the first Monday in September, through to the first Sunday in March. I have separate tabs for each year (named for the year it represents 2016, 2017, etc...).

Note* F1 happens to have the name of the tab in it. 2016 or 2017 or whatever year the sheet is for.

In BG2 I had this formula....

=SUMIFS('2020'!BG18:BG388,'2020'!A18:A388,">="&DATE('2020'!F1,9,1+7*1)-WEEKDAY(DATE('2020'!F1,9,8-2)),'2020'!A18:A388,"<="&DATE('2020'!F1,12,1+7*6)-WEEKDAY(DATE('2020'!F1,12,8-1)))+SUMIFS(BG18:BG388,A18:A388,">="&DATE(F1,1,1+1*1)-WEEKDAY(DATE(F1,1,8-2)),A18:A388,"<="&DATE(F1,3,1+7*1)-WEEKDAY(DATE(F1,3,8-1)))

In BG3 I have this formula....

=SUMIFS(BG18:BG388,A18:A388,">="&DATE(F1,3,1+7*1)-WEEKDAY(DATE(F1,3,8-2)),A18:A388,"<="&DATE(F1,9,1+7*1)-WEEKDAY(DATE(F1,9,8-1)))

Here is my problem....
A good example ie 2021. March 1st is a Monday. So BG2 & BG3 are both calculating the hours for the first week of march into their formula because the first Sunday is actually 6 days after the first monday.

I need the formula in BG2 to always start the day after the formula in BG3 (of the previous year) ends, and I need the formula in BG3 to always start the day after the formula in BG2 ends.

Putting it a different way to help with clarity....

BG2 needs to calculate the cells in BG from the first Monday of September (in the previous year tab) to the day before the first Monday of March in its own sheet.

BG3 Needs to calculate the cells of BG from the first Monday of March (on its own sheet) to the day before the first Monday of September on its own sheet.

Im thinking these formulas just need tweaking, but I dont fully understand how they work to know which numbers to change. With (F1,3,1+7*1) I know the F1 is the Year, 3 is the month. 1+7*1 loses me. I think Sunday = 1 and Monday = 2 and so on, but I dont understand what this is doing or how to keep them from overlapping each other on months that start on a Monday.

 

[SkipVought]

I understand. I've often done the same.

BTW, I'd recommend that you add

http://www.Tek-Tips.com

to your box of resource tools:

http://www.tek-tips.com/threadminder.cfm?pid=68

http://www.tek-tips.com/threadminder.cfm?pid=707

I post there as well.

Yes, people like what's familiar to them. Sometimes users need instruction on new and neat features. You wouldn't drive a Porsche the same as you would a Pontiac. Well you could, but why avoid the thrill?

I remember when Excel 2007 came out, the change in the GUI and other features were daunting. But people persevered and found new horizons and expanded capabilities.

Change: the story of civilization.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

but I still found certain years where the first of March or September fell on a Monday and the two formulas would include the same week in their calculations.

Not so! Only the first Monday (the From date) should be calculated. The To date is the From date of the other first Monday. Here's how it works with either SUMIFS() or SUMPRODUCT():

[tt][pre]
MAR =SUMPRODUCT((DateRange>=MarMON)*(DateRange<SepMON)*(BG_Range))
SEP =SUMPRODUCT((DateRange>=SepMON)*(BG-Range))+
SUMPRODUCT((NextYearDateRange<NextYearMarMON)*(NextYearBG_Range))
[/pre][/tt]
[pre][/pre]You never need to calculate a Sunday.

Check out this FAQ I just wrote, that explains how SUMPRODUCT works:
faq770-1959

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}