Slave Dynamic offset array function to a current mth & 12 mth period (Correct wording?)

Hi Team,

What I am looking to achieve is having a a block of data which includes a rather lengthy offset equation (line 6) move automatically with the current month so that when the month ends it automatically moves to the next months block of data and so on.

At the moment come months end I look to see at what point the new month starts and ends, then use the find and replace function to add the amount of lines (days in the month) required to take me to the next month. This is not an issue in itself, but my roster being what it is sometines I do not get to do this update until a week of the new month has passed.

Is there a way of slaving this to the computer time so that when Midnight ticks over, when the database opens in the new month, it moves the selection to reflect the current month?

The actual selection will never change. I am looking at sumproducts and patterns for the current month, and the previous 12 months.

I have seen something like SUMPRODUCT(--(DATESINMONTHS,=CURRENTMONTH) however, I am not sure how this is supposed to wrap around the equations in lines 3-6 that I am currently using to determine patterns.

I have several options and am not fussed on which one ends up being used.

Firstly, having the computer do it automatically would be the most preferred. This way there is no other user input to stuff up.

Second option is to manually tell the computer to look at a specific month and them manually enter that month in (or slave it to the computer which then makes it automatic I guess?)

Third option is to determine if there is a way of moving the infomation block down the required number of lines that will corrospond to the amount of days in the current month.

Fourth option would be to have the equations always look between lines 7 and 38 (being 31 days) for days absent in current month and between lines 7 and 1000 for patterns in the previous 12 months. What I would need to do then is delete the oldest months worth of data.

I have attached an example of the spreadsheet being used. Lines 3-6 are the calculation lines and would be of the most interest.

I do recall Ben_Hessel said that it was rather simple and could be done fairly easily, but I seem to have gotten a bit side tracked since that post and did not seek further information. Now I am quite stumped.

Thanks Guys,

Here are some options for rows 3 through 5
These regular formulas, copied across, return the values you're looking for

Please Login or Register  to view this content.

Is that something you can work with?

(Row_6 is a beast. I'm afraid I probably won't be able to address that one today.)

Hi Ron

Sorry I took so long to get back to you.

I have just tested this at home as I do not have sufficient privileges to muck around with the system dates on the work computer. Worked like a charm. Thank you so much. This will save much time and effort when I return. Having the spreadsheet automated means that I do not have to adjust everything at the beginning of each month. This can sometimes be a few days after the 1st day of the month rolls around. This then leaves the other supervisors working on incorrect information.

Have you (or anyone else out there) had the opportunity to wrap their heads around Row 6. As commented - this one is a beast. It essentially looks for patterns of sick days directly before or after annual Leave with a few minor variations like Ex days. Any assistance to automate this to the system date like you have with the code suggested for rows 3, 4 and 5 would be very much appreciated.

How do I mark this as solved with stars?