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]

Hi,

How about uploading a version of your workbook?

Skip,

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

 

[SkipVought]

In addition to a working Workbook example, I'd venture a simplification suggestion. Make ONE SHEET of source data that includes ALL the data for ALL years.

Then a sheet for the semiannual calculations for ALL years, that includes a column for year, a column each to calculate the first Monday of Mar & Sept, from which the previous day is readily accessible.

All those tabs are CRAZY!

Skip,

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

 

[SkipVought]

So here's an example/solution of what I might suggest.

The Summary sheet
[pre]
Year Mar-Sep Start Mar-Sep Hours Sep-Mar Start Sep-Mar Hours
...
[/pre]
...and the formulas in...
[tt]
Mar-Sep Start B2:
=DATE([@Year],3,1+7*1)-WEEKDAY(DATE([@Year],3,8-2))

Mar-Sep Hours C2:
=SUMPRODUCT((tHours[Date]>=[@[Mar-Sep Start]])*(tHours[Date]<[@[Sep-Mar Start]])*(tHours[total hours worked]))

Sep-Mar Start D2:
=DATE([@Year],9,1+7*1)-WEEKDAY(DATE([@Year],9,8-2))

Sep-Mar Hours E2:
=SUMPRODUCT((tHours[Date]>=[@[Sep-Mar Start]])*(tHours[Date]<B3)*(tHours[total hours worked]))
[/tt]



Skip,

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

 

[Kenja824]

The Workbook is not something for me to keep track of others information. It is something I am making for others to have their own copy and keep track of their hours. Keeping the years on separate tabs just seems the cleanest way for the user to work with it as there is a lot going on for each year in this. I am going to attempt to attach the workbook. I added a tab called DIRECTIONS that will have some explanation as to what everything is.

Note: The times listed and wage info and such is not pertaining anyone in particular. I added a standard $10 wage to make it easier to calculate in my head if the information is coming out right or not in my formulas. The times was from the hours I worked in the past but thrown in randomly, not worrying if it was correct for this. I just need to make sure formulas were working.

Please keep in mind. This is not completed and there are areas that are not formulated yet. On the "Comparisons" tab, this is just a copy of one from a previous version that I didnt like. I will be changing that sheet around some when I get to it.

Have fun. You will probably see plenty of ways to make this much easier and cleaner. I am no expert with this stuff. lol

 

[zelgar]

A problem I see with your spreadsheet is that for the September to March bonus, you're not evaluating the entire period of time. You're only looking at the data for the present year. Depending on the method you're wanting to evaluate the timeframes (e.g., is the March bonus the time period of Sept of prev. year to March of current year; or is it March of current year to Sept of current year) will determine how your formula need to be modified.

 

[SkipVought]

Okay, so your date formulas for the period ending (Sunday) date could be replaced with the Next Period Start Date minus 1.

For the Mar-Sep ending date simply use the Sep date minus 1 for the same year and for the Sep-Mar ending date use the Mar date minus 1 for the next year.

That answers your original question.

Skip,

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

 

[Kenja824]

Trying to put this a simple way....
We get two bonuses calculated by a percentage of the straight time hours we work for the previous six months.

In March 2017 we get one that is calculated from September 2016 to March of 2017.
In September 2017 we will get one calculated from March of 2017 to September 2017.

Unfortunately to make it more difficult, it doesnt run from the first of those months. I believe it is one week into the months. Or the first full week period (Monday through Sunday) of the month is the first week of the bonus period.

 

[Kenja824]

Hey Skip, that does and doesnt answer my question. The problem is I dont understand how that formula works completely, so your answer is a little tough for me to figure out. Sorry.

Could you show me what in the formula to change exactly please, then I can match your two answers together and make more sense of it. I hope. lol

 

[SkipVought]

[tt]
Sep-Mar
BG2: =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[highlight #FCE94F]+1[/highlight],3,1+7*1)-WEEKDAY(DATE('2020'!F1[highlight #FCE94F]+1[/highlight],3,8-2)))

Mar-Sep
BG3: =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-2)))
[/tt]



Skip,

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

 

[SkipVought]

I still think that your application is a [highlight #5C3566]maintenance nightmare[/highlight].

You could accomplish the same effect on one sheet much much simpler, as it just occurred to me that you are grabbing data from two sheets. UGLY!

Your control dashboard (rows 1-10) could have in-cell Data Validation Drop Down controls to select a specific year to report, that would control the other calculations in the dashboard. If I have time, I'll post an example using your data.

Skip,

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

 

[SkipVought]

You have a problem in BB calculating ST Hours, if you enter time in/time out as hours. You must convert Days to Hours.

Skip,

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

 

[Kenja824]

Hi Skip

I am not sure what you mean, as I do not have any formulas on column BB that I am aware of.

Also in case this may be confusing you, the times in and out are the times of the day in tenths. Punching in at 6:42 AM would be a 6.7 in the IN column (Column D). Punching out at 6:12 PM would be an 18.2 in column G

 

[SkipVought]

I'm sorry, column BF.

Well that explains it. There is no conversion required.

I'll have your workbook example reposted pretty soon, on one sheet. Not sure i can get all the formulas working as required.

I'd also suggest building a table for the bonus structure. Data is much easier to modify than data embedded in formulas.

Skip,

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

 

[SkipVought]

Here's your workbook back modified with data from 2010-2018.

E1 is SelectedYear, a Named Range. I use several. The formulas adjust to the SelectedYear.

It not "pretty" but it works on one sheet. Much MUCH easier to maintain and use, IMNSHO.

At the core are two Structured Tables: the main one is tHours. tYears is on the Factors sheet for reference for the in-Cell drop down.

Skip,

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

 

[Kenja824]

Thanks Skip

I looked it over some and found it interesting. I will have to play with it some when I get more time and make sure that filtering doesnt cause any trouble with protection and such. As well as I have to look over the formulas too. They look different to me. lol When I opened it, it said there were some links it could not keep. I will look at that.

Its funny. I use filtering for a lot of things, but never considered it for this. lol

Is it possible to make it so not every column can be filtered? That certain columns will only be manipulated by other columns filters, but cannot be the column that does the filtering? If that makes sense. lol

 

[SkipVought]

There is no "filtering" as in the drop down filters.

The formulas use one or more criteria. There are no problems having all the data, including calculated values, in the table. In fact any column that has formula SHOULD have the san formula in EVERY row of the table.

Skip,

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

 

[SkipVought]

So I'm trying to work with your current workbook design: one sheet for each year.

Working on the formulas in BG2 & BG3 and I discovered a problem with the scope of the actual data (formulas). Neither the data in column A or BG extended to row 388. THEREFORE, you will need to go into each and every year sheet and extend the formulas (really in all columns) to row 388.

BTW, If you were using Structured Tables for the data (A:BK AFAIK), formulas are all propagated through all rows and as you add rows, all formulas are propagated to the new row(s).

Once I fixed that, my tow formulas calculate data.
[tt]
MAR BG2:
=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-2)))

SEP BG3:
=SUMPRODUCT((BG18:BG388)*(A18:A388>=DATE(F1,9,1+7*1)-WEEKDAY(DATE(F1,9,8-2))))
+SUMPRODUCT((NextSheet(BG18:BG388,1))*(NextSheet(A18:A388,1)<DATE(F1+1,3,1+7*1)-WEEKDAY(DATE(F1+1,3,8-2))))
[/tt]

These formulas can be used on ANY sheet without modification, which is what you need in a multi-sheet circumstance.

I added a new function called NextSheet

Function NextSheet(rng As Range, PlusMinus As Integer)
'SkipVought Jan 24 2017
'returns a range on another sheet(+/- n) with the same relative row(s)/column(s)

    On Error Resume Next

    NextSheet = Sheets(rng.Parent.Index + PlusMinus).Range(rng.Address)

    If Err.Number <> 0 Then
        NextSheet = 0
    End If
End Function

BTW, the "links" message that you get, I get also, so it refers to links that you put in your workbook.

Skip,

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

 

[SkipVought]

Hello........

Skip,

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

 

[Kenja824]

Sorry Skip

I am doing this on the side of my normal work and sometimes when things get busy, I dont get to do much on it. Most of my time on it comes at the end of the day when most of the bosses are gone. lol On top of that, some of this is beyond my normal knowledge with excel.

In the future I think I will be adding your way of putting it all on one sheet and having a version like that available, but the couple people I talked to, said they would prefer to have the different years on different tabs. I think it is simply just what they have gotten used to in older versions of this I made.

At lunch today I did fix the formula problem. I tried your earlier formulas 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 sure if I just didnt do something right or what. I changed them to this.... though it made them a bit longer to do it....

BG2 = =IF(WEEKDAY(DATE('2016'!F1,9,1))=2,SUMIFS('2016'!BG18:BG388,'2016'!A18:A388,">="&DATE('2016'!F1,9,1+7*2)-WEEKDAY(DATE('2016'!F1,9,8-2)),'2016'!A18:A388,"<="&DATE('2016'!F1,12,1+7*6)-WEEKDAY(DATE('2016'!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))),SUMIFS('2016'!BG18:BG388,'2016'!A18:A388,">="&DATE('2016'!F1,9,1+7*1)-WEEKDAY(DATE('2016'!F1,9,8-2)),'2016'!A18:A388,"<="&DATE('2016'!F1,12,1+7*6)-WEEKDAY(DATE('2016'!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))))

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

In what little testing I could do, it seems to work right, but a half an hour isnt much time to really work on it. lol

I must have missed the formulas you recently added. I just saw them. I will give them a try as soon as I can, but unfortunately... or fortunately, tonight is bowling night.