Counting dates as months formula

[MattExcelLearner]

Hi all

Is it possible to count dates from multiple columns up as months.

This mean you can round up all the dates into a number in a month.

For example 6 dates recorded to show as a month with the total of 6 instead on another tab?

I have 4 columns which has dates (tab 2 full of data) in which I would like to add up and show on tab 1 the total number of dates inside a month and record that.

Not sure I'm making any sense, so adding an example to this post of what I am trying to do.

Any ideas or help would be most welcome

Many thanks

[TMS]

Suggest you post a sample with your expected results. Why are the months listed twice? It might make things simpler if the months listed on Sheet1 were actual dates formatted to show the month and maybe the year.

[kersplash]

I did something with a Pivot Table for one of the dates.

[MattExcelLearner]

Hi TMS

For example September should give a result of 8

Column E for September has 2
Column G for September has 0
Column I for September has 4
Column K for September has 2

This means in tab 1 the result for September should be 8

Does that help to make sense?

Regards

[MattExcelLearner]

pivot is a great idea but September is only showing as 2 rather than 8 that took place when doing an individual count from the data tab

[kersplash]

pivot is a great idea but September is only showing as 2 rather than 8 that took place when doing an individual count from the data tab

I only did one of the date fields. You would need a Pivot Table for each date field then add the month totals together.

[MattExcelLearner]

Tried to replicate with Define but couldn't, how did you get to just show the month rather than the dates in pivot?

[Bo_Ry]

Please try at Sheet1 B4 and drag down

=SUMPRODUCT((TEXT(Table_owssvr[[Initiate Health Check]:[Closure Health Check]],"mmmmyy")=A4&18+INT((ROWS(B$4:B4)-1)/12))*{1,0,1,0,1,0,1})

[TMS]

@Bo_Ry: neat solution

[Bo_Ry]

TMS, Thank you for the rep. I learn a lot by reading your Post, specially VBA part.

[FlameRetired]

A bit longer. Assumes the first set of 12 months is 2017.

=INDEX(FREQUENCY(CHOOSE({1,2,3,4},Table_owssvr[Initiate Health Check],Table_owssvr[Define and Design Health Check],Table_owssvr[Build and Deliver Health Check],Table_owssvr[Closure Health Check]),EOMONTH(DATE(2017+COUNTIF($A$4:A4,A4)-1,MONTH(A4&0),1),{-1,0})),2)

I like Bo_Ry's better.

[MattExcelLearner]

Thanks Bo_Ry this works really well on the example data count spreadsheet.

I have been trying for days to get it to work on the actual spreadsheet but it doesn't like the table name even though they are the same.

I also tried changing the column looks to match the example you gave in the actual spreadsheet but doesn't seem to work.

Would you know why?

Many thanks

[Bo_Ry]

Slightly change
From
=SUMPRODUCT((TEXT(Table_owssvr[[Initiate Health Check]:[Closure Health Check]],"mmmmyy")=A4&18+INT((ROWS(B$4:B4)-1)/12))*{1,0,1,0,1,0,1})


To B9
=SUMPRODUCT((TEXT(Table_owssvr_4[[Initiate Health Check]:[Closure Health Check]],"mmmyyyy")=B$8&$A8)*{1,0,1,0,1,0,1})

[MattExcelLearner]

Awesome BO_RY

I don't really understand the formula but got it to work for 2018 using the slight change.

I also did the same thing and used it for 2019 and that worked to, so that was amazing.

If I wanted to do the same thing, but this time instead of Initiate Health Check to Closure Health Check dates use the Initiate Sign off to Closure Sign off would that show the sign off dates per month?

Long shot but like this:

=SUMPRODUCT((TEXT(Table_owssvr_4[[Initiation Sign off]:[Closure Sign off]],"mmmyyyy")=B$8&$A8)*{1,0,1,0,1,0,1})

[Bo_Ry]

Yes, that should work,

The trick for {1,0,1,0,1,0,1} 1 is for column that you want to count and 0 is not count
Header.jpg

And Header in sheet KPI B$8&$A8 = Jan2018
TEXT(...,"mmmyyyy") "mmmyyyy" need to match with that header

[MattExcelLearner]

That's what I thought, it looks like it did work

Many thanks buddy, just got to get my =sumproduct to work now and it will be a happy morning hehe