Forum Discussion

ChrisFaye1's avatar
ChrisFaye1
Qrew Cadet
6 years ago

Log Edits Date/Time Formula

Hi! 

I'm trying to convert this formula into a date/time instead of only date record. 

The Status field currently has log edits turned on and I would also need to switch that log to include the time.

If(Contains([Status],"Closed Won"),
ToDate(Left(Right(NotRight([Deal Stage Tracking],"Closed Won"),"["),9)),null)

Is this an easy use of ToTimestamp that I'm just failing at?

Thank You!
Chris

------------------------------
Chris
------------------------------
Formulas and functions

5 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    Try this

    var text DateAndTime = Left(Right([Deal Stage Tracking],"["),18);
    var date TheDate = ToDate(Left($DateAndTime," "));
    var timeofday TheTime = ToTimeOfDay(NotLeft($DateAndTime,9));


    ToTimestamp ($TheDate, $TheTime)

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • ChrisFaye1's avatar
      ChrisFaye1
      Qrew Cadet
      6 years ago
      Thanks, Mark! That worked perfectly!

      Whenever we get out of this quarantine and are able to attend the next QB Event I owe you dinner or at least some drinks. You're so consistently helpful and on it! 

      Thank You Again!

      ------------------------------
      Chris
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        6 years ago
        OK, in 2021 we can meet at the EMPOWER open bar and you can buy me a (free) Beer.  I'm guessing it will be in San Diego again.

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------
    • DonLarson's avatar
      DonLarson
      Qrew Elite
      5 years ago
      Mark,

      Thanks for this.  I had similiar issue parsing out the time stamp info from an appended notes field.  Using ToDate on the whole object returned a result offset by four hours.  I am guessing that there was a GMT to Time Zone issue.  Your multi part process saved it.

      In case anybody else needs it here is the entire sequence to get the DTS from an appended notes field, [Sales Notes], with the last note being at the top.

      var text FirstEdit = Left([Sales Notes],"]");
      var text SecondEdit = Right($FirstEdit, "[");
      var text ThirdEdit = Left($SecondEdit,18);
      var date TheDate = ToDate(Left($ThirdEdit," "));
      var timeofday TheTime = ToTimeOfDay(NotLeft($ThirdEdit,9));
      ToTimestamp ($TheDate, $TheTime)

      ------------------------------
      Don Larson
      Paasporter
      Westlake OH
      ------------------------------