First, please do not quote prior posts in full in your response this practice simply clutters up the board...only quote necessary parts so as to maintain logical flow to thread (ie if answering posts out of sequence).
The fact that the dates cross months is of no real / fundamental concern given this can be handled via formulae (as shown by earlier formula).
What should be of real concern to you is the fact that your data is stored in an illogical manner and will result in very inefficient methods for analysis.
If you wish to conduct efficient analysis on mass data with Excel as the data warehouse then you really must strive to store data in a logic and coherent manner
ie think along the lines of database storage.
Using your sample file as a discussion point... and specifically "Data" sheet
IMO you should be storing these data points in a table with the following structure ("columns"):
each value in your present matrix (C6:T37) would in the above set-up be represented by a separate row / transaction in your listing, eg:
You will find you can analyse the above table far (far) more efficiently - eg Pivot Table.
Bookmarks