Getting my metrics form to tally

Devon

Hi all,

I am trying to have my metrics sheet to total the # of visits that came in for today. The below is generating as 0, though there data to pull from.

What am I doing wrong?

I am looking forward to getting this to calculate, and then eventually figure out how to set this up for a weekly and monthly snapshot next for a quick overview.

Thank you!

Paul Newcome

What data do you have in [Primary Column]?

jmyzk_cloudsmart_jp

Hi @Devon

As we do not get the {Primary Column} date, I am answering possible solutions for {CFF created date}, TODAY() condition part.

I think you have two issues,  “Using TODAY() in Rows Problem” and” Comparing UTC-based Created Date with Local Time-based TODAY()” problem.

Published Dashboard Link of above image :

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

• Using TODAY() in Rows Problem:
  • If you employ the TODAY() function across rows, especially when working with column functions, every row will use the same date value as per TODAY().
  • Set the Today Date manually or Scheduled TODAY() date Recording:
    • Set up automation in Smartsheet to log the current date every day at a specific time, such as 7 AM. This provides a consistent reference point.
• Comparing UTC-based Created Date with Local Time-based TODAY():
  • Understanding Created Date's Time Zone:
    • While the Created Date in Smartsheet displays values according to your local time zone, the underlying value is stored in UTC (Universal Time Coordinated) or GMT (Greenwich Mean Time).
  • Comparison Challenges:
    • Directly comparing the Created@row value or date-only value of the Created Date (DATEONLY(Created@row)) with TODAY(), which represents your local timezone's date, might lead to inaccurate results because TODAY() value is local where as Created@row value is UTC based.
  • Time Zone Adjustment:
    • To ensure accurate comparisons, it's essential to convert Created@row or  DATEONLY(Created@row) to your local timezone before making any comparisons with TODAY().
    • Method1: Time Zone Adjustments formula using Created Display Value
      • Created Display (a helper column: Column Type Text/Number)
        • =LEFT(Created@row, 8)
      • Created Date by Display (Column Type: Date)
        • =DATE(2000 + VALUE(RIGHT([Created Display]@row, 2)), VALUE(LEFT([Created Display]@row, 2)), VALUE(MID([Created Display]@row, 4, 2)))
    • Method2: Time Zone Adjustments formula using GMT offset value (GMT# as saved at Sheet Summary)
      • =IF([Hour (24)]@row - GMT# < 0, [DATEONLY (Created)]@row + 1, IF([Hour (24)]@row - GMT# >= 24, [DATEONLY (Created)]@row - 1, [DATEONLY (Created)]@row))
      • When comparing with TODAY():
        1. If subtracting GMT# from the hour in [Hour(24)] results in a time before the start of the day ( <0), add 1 day to [DATEONLY (Created)].
        2. If it results in a time beyond the end of the day (>=24) , subtract 1 day from [DATEONLY (Created)].
        3. Otherwise, simply use the date from [DATEONLY (Created)].
      • Please note the 1. case happens GMT +  Time Zone (example: Tokyo GMT +9) while the 2. case  Happens GMT - Time Zone (example: Seattle GMT -7).

GMT + Time Zone: If GMT time ([Hour(24)]@row - GMT#) is negative, one day must be added to DATEONLY(Created).

GMT - Time Zone: If GMT Hour ([Hour(24)]@row - GMT#) becomes negative, need to add a day to DATEONLY(Created)