Skip to main content

Counting days since previous date

  • September 20, 2018
  • 4 replies
  • 55 views
E

I have two tables (Ad Schedule & Ad Copy). Ad Schedule contains rows of ads (linked records to ‘Ad Copy’ along with lookup columns) with dates when the ads went live. The Ad Copy table contains the ad content that the Ad Schedule table pulls from.

I need a way to look at the last date the ad went live in the Ad Schedule table (this is a date column) and calculate the number of days since the ad content was last used into a formula field in the Ad Copy table. The number of days since the ad was last posted should show up in the Ad Content table.

I was working with TONOW({Ad Schedule Post date}), but “Ad Schedule Post date” is a Link to Another Record field and can have multiple dates this doesn’t work. Is there a way to just grab the last item in the array (assuming Link to Another Record field is actually an array) and then calculate TONOW() on one of the items (the most recent date) in the field?

    This topic has been closed for replies.

    4 replies

    J
    Forum|alt.badge.img+18

    I think what you want here is a Rollup field using the MAX() rollup function.

    You’ll point the Rollup field at the linked record field (Ad Schedule) and the field from the linked record that you want to rollup (Ad Scheduled Post Date). Then use MAX(values) as the function.

    That will return the most recent date from the list of dates. Then you should be able to perform your TONOW() function with reference to that Rollup field.

    Post back if you continue to have issues with that.

      E
      • Erik_Peterson
      • Author
      • New Participant
      • September 21, 2018

      I think what you want here is a Rollup field using the MAX() rollup function.

      You’ll point the Rollup field at the linked record field (Ad Schedule) and the field from the linked record that you want to rollup (Ad Scheduled Post Date). Then use MAX(values) as the function.

      That will return the most recent date from the list of dates. Then you should be able to perform your TONOW() function with reference to that Rollup field.

      Post back if you continue to have issues with that.


      Excellent! That is what I needed. However, it leads to another question.

      TONOW() shows the number of hours, but I really only need days. Anything less than 23.99hrs would be 0 days and so on. Do I need to wrap another function around the TONOW() in order to round to days?

      Ideally I would be able to say something like this:

      IF({Latest ad} = BLANK(),
      ‘ :new: NEW’,
      IF({Latest ad} < TODAY(),
      IF({Latest ad} >= 46 days,
      ‘ :large_blue_circle: READY! ’ & ’ ’ & TONOW({Latest ad},
      IF({Latest ad} <= 45 days,
      ‘ :red_circle: HOLD’ & TONOW({Latest ad},)
      ), ‘ :large_orange_diamond: FUTURE’ & TONOW({Latest ad}) & ’ from today’
      )
      )

      J
      Forum|alt.badge.img+18

      Excellent! That is what I needed. However, it leads to another question.

      TONOW() shows the number of hours, but I really only need days. Anything less than 23.99hrs would be 0 days and so on. Do I need to wrap another function around the TONOW() in order to round to days?

      Ideally I would be able to say something like this:

      IF({Latest ad} = BLANK(),
      ‘ :new: NEW’,
      IF({Latest ad} < TODAY(),
      IF({Latest ad} >= 46 days,
      ‘ :large_blue_circle: READY! ’ & ’ ’ & TONOW({Latest ad},
      IF({Latest ad} <= 45 days,
      ‘ :red_circle: HOLD’ & TONOW({Latest ad},)
      ), ‘ :large_orange_diamond: FUTURE’ & TONOW({Latest ad}) & ’ from today’
      )
      )


      Simply divide the results of the TONOW() function in your formula by 24 to have it return days, and then concatenate & " days" after it (you need the space before the word “days” inside the quotes so that you get a space between the number and the word “days” in the return output).

        E
        • Erik_Peterson
        • Author
        • New Participant
        • September 21, 2018

        Thank you. I think I have those sorted out.

          Terms & ConditionsAccessibility statement