Date Formula

Kim McNamara

Hi 

I am trying to count the number of items that are for a particular month and year.

eg:  I would like to count the number of items submitted for the month of Jan 2018

the column I am wanting to pull the data from is a date column

Chris McKay

Hi Kim,

As per the screencap:

Month - dropdown with January, February etc.

Year - dropdown with 2017, 2018 etc. (as per your requirements)

Count - formula: =COUNTIF([Submitted Merged]:[Submitted Merged], [Search Date]1)

Submitted Date: Just a regular date field

Search Date - formula: =IF(Month1 = "January", 1 + " " + Year1, IF(Month1 = "February", 2 + " " + Year1, IF(Month1 = "March", 3 + " " + Year1, IF(Month1 = "April", 4 + " " + Year1, IF(Month1 = "May", 5 + " " + Year1, IF(Month1 = "June", 6 + " " + Year1, IF(Month1 = "July", 7 + " " + Year1, IF(Month1 = "August", 8 + " " + Year1, IF(Month1 = "September", 9 + " " + Year1, IF(Month1 = "October", 10 + " " + Year1, IF(Month1 = "November", 11 + " " + Year1, IF(Month1 = "December", 12 + " " + Year1, ""))))))))))))

Submitted Merged - formula: =IF(LEN([Submitted Date]1) > 0, MONTH([Submitted Date]1) + " " + YEAR([Submitted Date]1), "")

You can then hide the Search Date and Submitted Merged columns when you're satisfied it works OK.

I used Month & Year dropdowns to provide a nicer user experience, but you can simplify the whole thing by just asking the user to pick a date.

Hope this helps.

Kim McNamara

Hi Chris

Thank you for your help but unfortunately I cannot get the formulas to work.  I keep getting an error.

I am sure I am missing a detail but can't seem to identify it.

Count formula shows a blocked error

Search Date formula shows a unparseable error

Submitted Marge formula accepts the formula but shows no results

I have created columns as per your suggestion and added the formulas to the first row of each column

Kim