Does anyone know why the COUNTIFS function used with a DATE range stops counting things done at 7PM?

Avery Rueckheim

I am currently using this function:

=COUNTIFS({Closed}, <=DATE(2020, 4, 30), {Closed}, >=DATE(2020, 4, 1))

to calculate the number of projects closed every month. When I compare the number that is calculated to the number of rows in the sheet that it is referenced, it doesn't match because everything that was closed after 7PM is counted as the next day/month. Therefore, the one's that are closed after 7PM on April 30th, are then added to May's total.

Also fyi, in the sheet and field I'm referencing I'm using an auto-number system date field. (Not sure if this matters)

Paul Newcome

It has to do with time zones. There are a few solutions to the time zone issue floating around here in the Community. Feel free to do a search yourself, and I will see if I can locate one of them as well and provide a link.

The basic idea is that you pull the time and use it to change the date and then use your COUNTIFS on this new date column.

Paul Newcome

Happy to help. 👍️

I had completely forgotten about the noon issue. We can actually shorten your formula up a bit by using an OR statement inside of the AND.

=DATEONLY(Created@row) - IF(AND(FIND("P", Created@row) > 0, OR(VALUE(MID(Created@row, 10, FIND(":", Created@row) - 10)) >= 7, VALUE(MID(Created@row, 10, FIND(":", Created@row) - 10)) = 12)), 1)

Paul Newcome

It has to do with time zones. There are a few solutions to the time zone issue floating around here in the Community. Feel free to do a search yourself, and I will see if I can locate one of them as well and provide a link.

The basic idea is that you pull the time and use it to change the date and then use your COUNTIFS on this new date column.

Paul Newcome

HERE is a thread that we could use to start a solution with.

Try putting this into a date type column:

=DATEONLY([Created Date]@row) - IF(AND(VALUE(MID([Created Date]@row, 10, FIND(":", [Created Date]@row) - 10)) >= 7, FIND("P", [Created Date]@row) > 0), 1)

Paul Newcome

@lewis hamilton You are referring to features not available in Smartsheet, and the issue is not that the function is operating slowly. The issue is that the function is leaving out specific data that should be included.

Sean Morgan

Hello @Avery Rueckheim & @Paul Newcome ,

Great solution Paul, and you are correct. This is due to Time Zone differences.

Smartsheet servers use UTC, so this will naturally have a knockback effect on Date based Formulas.

As Paul has mentioned, there are additional Formula functions to help work around this, and our great Community Members, such as Paul, have created many for individual users solutions

Kindest Regards

Sean

Avery Rueckheim

@Paul Newcome @Sean Morgan

Thank you. That was very insightful.

However, I am unable to use a manual formula. I need to use the auto-generated Created time because projects that have been closed on one sheet are set up to move to another sheet and the auto-generated Created time allows for the timestamp of when it was closed/moved there. This calculation is key to show a live update of the projects closed on a Dashboard.

It would be ideal if there is a way to make the timezones to be local for date based formulas. Having a default of UTC as the only Timezone restricts all Smartsheet users globally that fall ouside of the UTC Timezone.

Paul Newcome

Which sheet are you trying to pull your count from?

Avery Rueckheim

@Paul Newcome

I have two sheets:

One live sheet where we are uploading data automatically to where we see all of our active/inactive projects.

One completed sheet, where we move all the completed/closed projects to with automation. I am pulling my count from this sheet that contains the additional Created column. This column is the system auto-generated column that shows the date and time in which the completed project was added to the sheet.

Paul Newcome

You should be able to insert an additional date column and enter the formula, and auto-fill should pull the formula into new rows as they are added from your automation.

Avery Rueckheim

I had issues with it auto-filling before when it is added, but it seems to work now!

Another quick questions is how to include things closed during the noon hour (12PM).

Your formula works great, however since 12 is larger than 7, it is not considered in your formula.

Avery Rueckheim

I figured it out! I simply added the formula you subracted and replaced ">= 7" with "= 12".

=DATEONLY(Created@row) - IF(AND(VALUE(MID(Created@row, 10, FIND(":", Created@row) - 10)) >= 7, FIND("P", Created@row) > 0), 1) + IF(AND(VALUE(MID(Created@row, 10, FIND(":", Created@row) - 10)) = 12, FIND("P", Created@row) > 0), 1)

Thank you so much for your help @Paul Newcome!

Paul Newcome

Happy to help. 👍️

I had completely forgotten about the noon issue. We can actually shorten your formula up a bit by using an OR statement inside of the AND.

=DATEONLY(Created@row) - IF(AND(FIND("P", Created@row) > 0, OR(VALUE(MID(Created@row, 10, FIND(":", Created@row) - 10)) >= 7, VALUE(MID(Created@row, 10, FIND(":", Created@row) - 10)) = 12)), 1)