Skip to main content
Question

Most Recent WeekDay Field

I need help constructing a formula field (if possible). Each day I import records to a table from another platform, and include a date apart of that record called "Data Date" so that users are able to decipher how recent that data is...

The next day, I import more records, and again, the field "Data Date" includes today's date. So when a user is looking at various reporting, they can filter by data date and see the most recent info.

Most of the time, users want to see records where the Data Date = today. Which is how I've filtered most reports. But if a user ever wanted to see the report on the weekend (which may be very rare), the report is blank.

Are there any fields I can create to look at the data date, and report back the most recent weekday?
      This topic has been closed for replies.

      try this as a formula date field

      Case(DayOfWeek([date field]),  // the day of week is 0 for Sunday and counts up from there

      0,[date field]-Days(2), // if Sunday, subtract 2 days to get Friday.
      6,[date field]-Days(1), // if Saturday subtract 1 day
      [date field]) // else just use the date
      Thank You, --- I'm a bit closer, but not 100% yet.



      I have a field now for MostRecentWeekDay, based on the formula .

      Case(DayOfWeek([date field]),  // the day of week is 0 for Sunday and counts up from there

      0,[date field]-Days(2), // if Sunday, subtract 2 days to get Friday.
      6,[date field]-Days(1), // if Saturday subtract 1 day
      [date field]) // else just use the date


      I'm having trouble writing a report ---My report says, filter on:

      If MostRecentWeekDay = Today

      Or MostRecentWeekDay = 1 day in the past.


      This will show the user all records from yesterday (before I upload the new day's data)... But once I do, I want to get rid of the prior day's data from this report.


      Any ideas?
      OK, I get it.
      I think that the answer is to create another field called [Today or the Previous Friday].
      The formula will be similar,

      Case(DayOfWeek(Today()),  // the day of week is 0 for Sunday and counts up from there

      0,Today()-Days(2), // if Sunday, subtract 2 days to get Friday.
      6,Today()-Days(1), // if Saturday subtract 1 day
      Today()) // else just use the date

      Then filter the report where

      [MostRecentWeekDay] is equal to the value in the field [Today or the Previous Friday]
      Terms & ConditionsAccessibility statement