Calander month confusion

The Dan

Good morning, Brains Trust!

I'm having issues with a formula and was hoping that people smarter than me might have some answers.

Objective,

To find out how many lines within a cell have a date that falls within the current calendar month.

Info,

The cell is named "Date added"

I have tried to use the formula "=COUNTIFS({Date added}, >=DATE(TODAY(), 1, 1), {Date added}, <=EOMONTH(TODAY(), 0))"

Essentially, these are my thoughts,

COUNTIFS: This function is used to count the number of cells that meet multiple criteria.

{Date added}, >=DATE(TODAY(), 1, 1): This criteria checks if the date in the "Date added" column is greater than or equal to the first day of the current month. DATE(TODAY(), 1, 1) gives the first day of the current month.

{Date added}, <=EOMONTH(TODAY(), 0): This criteria checks if the date in the "Date added" column is less than or equal to the last day of the current month. EOMONTH(TODAY(), 0) gives the last day of the current month.

This formula always returns the value #UNPARSEABLE.

I can't seem to see what I am doing wrong, and any help would be appreciated.

Regards,

Daniel

AravindGP

Hi Daniel,

There is no EOMONTH function in Smartsheet. You could try this. You will need to add a helper column to capture the month, using the =MONTH({Date added}), which will give you a month number. You can then add another column to capture the month end using this formula =IF(OR([R1]@row = 1, [R1]@row = 3, [R1]@row = 5, [R1]@row = 7, [R1]@row = 8, [R1]@row = 10, [R1]@row = 12), DATE(YEAR(TODAY()), MONTH(TODAY()), 31), DATE(YEAR(TODAY()), MONTH(TODAY()), 30)). The month start formula should be =DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

Your final formula would be =COUNTIFS({Date added}, >=DATE(YEAR(TODAY()), MONTH(TODAY()), 1), {Date added}, <={Month End helper column}