Count if formulas with system created dates

Michele @ My School DC

Hi Everybody,

We use webforms for my team to log in calls to our Hotline. Due to popular demand I recently changed from having the call date entered on the form to the system created date.  Now I can no longer use my handy formula to keep tabs on how the call are going each day.  I used to have a formula for  each staff member that kept a running tally: ="Michele Daily=" + COUNTIFS(Staff:Staff, "Michele", [Call Date]:[Call Date], TODAY()).  I loved this. The new formula, with the system created date, does not work: ="Michele Daily=" + COUNTIFS(Staff:Staff, "Michele", [Created (Date)];[Created (Date)], TODAY()).

 

Does anyone who loves smartsheet formulas as much as I do have a suggestion?

Thanks folks,

Michele

 

Joel Johnson

I haven't thought this one through all the way, but perhaps you could add back in your sheet a date column that is called 'Call Date' and in that column pull just the date out of the 'Created' Column using the formula:  =DATEONLY(Created1).

 

This will pull just the date out and ignore all of the rest of the time stamp, and you should beable to do calculations with it now.

Michele @ My School DC

Thanks Joel, I tried that first step, creating the column and putting in the formula you suggested, and its "unparseable."  I tried a couple of variations (like the formula for TODAY) but it isn't working.

 

Is it wrong that I find the search for the correct formula fun?  I appreciate your looking at this.

Zack S

Hello Michele, 

 

The workaround Joel mentioned will work, but there is a caveat you'll want to keep in mind. While the date in the Created column will display accurately based off your timezone, running a formula off that column will refer to GMT time in terms of what it produces for a value.

 

This means that once it is the next day in England, or the GMT timezone, it will return the next day in the result. For example it may be later in the day on October 19th, which is what the Created column will state, but the formula will produce a date of Octover 20th as that is the current date in the GMT timezone.  

 

This is something we are addressing in a new verison of our formula engine, but that won't be available for a while. 

 

In regard to the formula giving you the Unparsable error, it's tough to say what may be causing that, but the formula Joel provided should work, as long as you updated the Date Refference. 

 

If you are still having trouble, please contact support@smartsheet.com and let them know you are receiving an error and we'll be happy to troubleshoot the nature of the formula error. 

 

-Zack

Michele @ My School DC

Morning Zack,

Thanks for the dateline tip.  I tried again with Joel's formula, and will send it to the support team.

I appreciate your taking the time to help.

Best,

Michele