Picking calendar month out of rolling weeks...

Please excuse the lack of example file, I'm on my phone and won't be back home till much later today.

I have a sheet that consists of 6 blocks of 7 rows.

These blocks each represent 1 week running Monday to Sunday. The first cell in each row is populated with the date in "dd/mm/yyyy" format, but set to display as "ddd, dd/mm/yyyy".

Due to the fact there can be 31 days in a month, and that day 1 could be the last day of the week, I have 6 blocks to take into account of the overlap.

What I want to do is to be able to calculate totals just for the calendar month, and not the days before or after.

Due to restrictions of the end user, VBA is out of the question.

Is it possible for "TRUNC()" to return the day value from the whole date?

If so, I might be able to use conditional statements in a helper column to isolate the days I want.

Originally Posted by AliGW

Without a sample file, it's not particularly easy, however you can use SUMIFS with EOMONTH to achieve a sum formula that will exclude the outlying dates.

I'll need to wait to see your sample before I can flesh this out.

File now attached (I hope) - only got 5 weeks in it at the moment, but there will be a 6th.

Originally Posted by bsalv

Please Login or Register  to view this content.

null-values are hidden in the worksheet via "options

Trying to figure out what this does / how it works

Originally Posted by bsalv

Please Login or Register  to view this content.

null-values are hidden in the worksheet via "options

I'm not sure this is quite what I'm after... I want to return the day value from the date, i.e. 11 from 11/03/2021

And it looks like I have been overthinking and missing the obvious, because if A1 has the data 11/03/2021

then =DAY(A1) should return 11

DOH!

So I can easily run an =IF(DAY(A1)>... to get what I need.

Thanks for the help anyway guys