Most Recent WeekDay Field

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress
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?
Photo of Ashley Asante

Ashley Asante

  • 736 Points 500 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
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
Photo of Ashley Asante

Ashley Asante

  • 736 Points 500 badge 2x thumb
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?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
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]