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
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
-
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
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives