Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

how to write formulas to get the answer of "?" in pic below.

The date are in string (manually typed)

I want to count how many different date in a month

for example 22-jan-20 and 21-jan-20 are counted as 2

Please help me for the smartsheet formula

Best Answer

Answers

  • Hi @Michael Giovanni

    You can use a combination of a COUNTIF formula and a MONTH function (using IFERROR to get rid of any blank cells).

    Try this for your January count:

    =COUNTIF(Dates:Dates, IFERROR(MONTH(@cell), 0) = 1)

    Then to change what month you're looking for, just change the 1 at the very end of the formula to be that month's number. For example, if I was looking for April, I'd use this:

    =COUNTIF(Dates:Dates, IFERROR(MONTH(@cell), 0) = 4)


    Let me know if this works for you!

    Cheers,

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • My apologies Michael, I just read that your dates are in a string, not in a Date Type of column. The formula above is for a Date Type of column.

    In this instance, you would need to use CONTAINS to check and see if your dates column contains a certain word. Please note that I've titled my column "Dates" for the range, so you will need to swap this out for your current date column name:

    =COUNTIF(Dates:Dates, CONTAINS("Jan", @cell))

    Then for each Month, you would want to change the value "in these" that you are searching for:

    =COUNTIF(Dates:Dates, CONTAINS("Apr", @cell))


    Cheers,

    Genevieve

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • Thank you for responding

    The formula doesn't work. But even if it is working, I don't think that I will get the answer that I want.

    If you look at the example pic, there are 3 "January" but only 2 with different date.

    I don't want to count the same date.

  • This one works, but it will count month with the same date.

    The problem is I don't want to count multiple date

  • Marvelous, this works well. I know that it will work with distinct, but apparently I'm not smart enough to write it in this order.

    Thank you very much!!! Really appreciate your help.

    🍪 here's a cookie for you.

  • Haha no problem at all! Thank you for the cookie 😋

    Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions