Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

using dates in countif formula

07/19/17 Edited 12/09/19

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???

Comments

  • Mike WildayMike 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. 

  • 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 WildayMike 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 WilliamsJ. Craig Williams Top Contributor

    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

This discussion has been closed.