Getting my metrics form to tally

Options

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!


Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Options

    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 :


    • 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)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!