HELP WITH EXCEL FORMULA PLEASE
HELP WITH EXCEL FORMULA PLEASE
(OP)
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.
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.





RE: HELP WITH EXCEL FORMULA PLEASE
How about uploading a version of your workbook?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: HELP WITH EXCEL FORMULA PLEASE
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,
for a NUance!
RE: HELP WITH EXCEL FORMULA PLEASE
The Summary sheet
...and the formulas in...
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]))
Skip,
for a NUance!
RE: HELP WITH EXCEL FORMULA PLEASE
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
RE: HELP WITH EXCEL FORMULA PLEASE
RE: HELP WITH EXCEL FORMULA PLEASE
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!
RE: HELP WITH EXCEL FORMULA PLEASE
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.
RE: HELP WITH EXCEL FORMULA PLEASE
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
RE: HELP WITH EXCEL FORMULA PLEASE
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+1,3,1+7*1)-WEEKDAY(DATE('2020'!F1+1,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)))
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: HELP WITH EXCEL FORMULA PLEASE
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,
for a NUance!
RE: HELP WITH EXCEL FORMULA PLEASE
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: HELP WITH EXCEL FORMULA PLEASE
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
RE: HELP WITH EXCEL FORMULA PLEASE
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!
RE: HELP WITH EXCEL FORMULA PLEASE
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!
RE: HELP WITH EXCEL FORMULA PLEASE
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
RE: HELP WITH EXCEL FORMULA PLEASE
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!
RE: HELP WITH EXCEL FORMULA PLEASE
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.
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))))
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
CODE
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 FunctionBTW, the "links" message that you get, I get also, so it refers to links that you put in your workbook.
Skip,
for a NUance!
RE: HELP WITH EXCEL FORMULA PLEASE
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: HELP WITH EXCEL FORMULA PLEASE
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. :)
RE: HELP WITH EXCEL FORMULA PLEASE
BTW, I'd recommend that you add 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!
RE: HELP WITH EXCEL FORMULA PLEASE
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():
MAR =SUMPRODUCT((DateRange>=MarMON)*(DateRange<SepMON)*(BG_Range)) SEP =SUMPRODUCT((DateRange>=SepMON)*(BG-Range))+ SUMPRODUCT((NextYearDateRange<NextYearMarMON)*(NextYearBG_Range))You never need to calculate a Sunday.
Check out this FAQ I just wrote, that explains how SUMPRODUCT works:
FAQ770-1959: How do formulas like SUMIFS(), COUNTIFS() or SUMPRODUCT() work as multiple criterria aggregations?
Skip,
for a NUance!