Countifs off by one

I am trying to get a count for a date range, but am getting a number that is one less compared to the filter.

Current Tuesday Formula: =IFERROR(TODAY() + (3 - WEEKDAY(TODAY())) - IF(OR(WEEKDAY(TODAY()) = 1, WEEKDAY(TODAY()) = 2), 7, 0), "")

Previous Tuesday Formula: =IFERROR(TODAY() - 7 + (3 - WEEKDAY(TODAY() - 7)) - IF(OR(WEEKDAY(TODAY() - 7) = 1, WEEKDAY(TODAY() - 7) = 2), 7, 0), "")

Total Count Formula: =COUNTIFS([Date Entered by Vendor]:[Date Entered by Vendor], AND(@cell >= [Previous Tuesday]#, @cell <= [Current Tuesday]#))

Here is the row that is not being caught in the formula.

Find more posts tagged with

Formulas

Accepted answers

jmyzk_cloudsmart_jp

Hi @Nick0000

The time zone issue sometimes causes the discrepancy.

For example, I set my Time Zone setting to GMT+9.

So, if I apply the DATEONLY function to the system column, Created, I get the previous date if the time is before 9:00 AM.

The Created displays the date in my time zone (GMT+9), but when we use a function like the DATEONLY, the calculation is based on UTC or GMT zone, so 8:30 AM in my time zone is still yesterday in London. So, the DATEONLY result is one day before.

https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones

Time zones and system columns
System columns always use UTC in the back-end, while the display value matches the time zone of the last person to access the sheet. This means that, although the display value is in the sheet-viewers time zone, formulas and API calls using system column data may return based on the UTC time zone.

So, when you use the system date column to determine the date, you need to consider the time zone issue.

An easy way to get the Date part is to use the TEXT functions, as the system date columns display the date according to your time zone.

You can use a formula like this to get a local date.

=DATE(2000 + VALUE(MID([Date Entered by Vendor with Time]@row, 7, 2)), VALUE(LEFT([Date Entered by Vendor with Time]@row, 2)), VALUE(MID([Date Entered by Vendor with Time]@row, 4, 2)))

By the way, you can simplify the Current and Previous Tuesday formulas as these;

Current Tuesday Formula:
=TODAY() + 3 - WEEKDAY(TODAY())
Previous Tuesday Formula:
=[Current Tuesday]# - 7

https://app.smartsheet.com/b/publish?EQBCT=4428c1dd326a42b69d7afb278d2d08f8

Filter Display

All comments

jmyzk_cloudsmart_jp

Hi @Nick0000

The time zone issue sometimes causes the discrepancy.

For example, I set my Time Zone setting to GMT+9.

So, if I apply the DATEONLY function to the system column, Created, I get the previous date if the time is before 9:00 AM.

https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones

Time zones and system columns
System columns always use UTC in the back-end, while the display value matches the time zone of the last person to access the sheet. This means that, although the display value is in the sheet-viewers time zone, formulas and API calls using system column data may return based on the UTC time zone.

So, when you use the system date column to determine the date, you need to consider the time zone issue.

An easy way to get the Date part is to use the TEXT functions, as the system date columns display the date according to your time zone.

You can use a formula like this to get a local date.

=DATE(2000 + VALUE(MID([Date Entered by Vendor with Time]@row, 7, 2)), VALUE(LEFT([Date Entered by Vendor with Time]@row, 2)), VALUE(MID([Date Entered by Vendor with Time]@row, 4, 2)))

By the way, you can simplify the Current and Previous Tuesday formulas as these;

Current Tuesday Formula:
=TODAY() + 3 - WEEKDAY(TODAY())
Previous Tuesday Formula:
=[Current Tuesday]# - 7

https://app.smartsheet.com/b/publish?EQBCT=4428c1dd326a42b69d7afb278d2d08f8

Filter Display

Nick0000

Thank you!

jmyzk_cloudsmart_jp

Happy to help!😁

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.