using dates in countif formula

mvelasquez

Hi all,

I have an issue, I need to use a collumn with autonumber system format, in this case i have used date.

the problem ocurres when i need to count all rows created in a specific date, it seems that the systems does not count the dates with hours after 5:00 pm  or assignes the next day date if the hours surpases 5:00 pm 

how can avoid this??, do somebody else have the same issue???

Mike Wilday

If you convert the system date to ignore the time values do you get a date that you can work with? 

In the following formula replace the Created28 with your column name and corresponding row number. If your column name contains spaces enclose it in brackets like this: [Created Column]28.

=DATE(YEAR(Created28), MONTH(Created28), DAY(Created28))

The formula should convert your system date into a date that doesn't contain time and should then be counted in your formula. 

mvelasquez

Hi Mike, thanks for your answer, i tried that formula, but it still is assigning the next day

for example i Have 07-18-17 07:24 p.m. if i use your formula or =, or dateonly, the result is 07-19-17

 

Mike Wilday

I'll let someone else pipe in, but try checking your Account > Settings > Personal settings and make sure your time zone is accurately selected.

You might also try setting your workday to 24 hours and see if that time is then counted on the same day. This is really odd. 

It might also be an issue where the time being grabbed is actually the server time rather than the time based on your timezone. Which could affect the output you are getting. I would suggest writing into your formula something based on the time, but there are no Time formulas in Smartsheet. 

 

J. Craig Williams

The problem is how Smartsheet handles the UTC time. It is a known issue but I do not know if they plan to fix it.

Craig