top of page
Search

Generate Previous Dates in a Knack Database

How I Calculated the Previous Week in Knack (Without Losing My Mind)

If you’ve ever tried to calculate “last week” in a Knack database, you probably thought it would be simple… until you actually tried it.

That was exactly my experience.


I recently worked on a CRM where the client wanted a clean way to look at activity from the previous week (Sunday through Saturday)—no matter what day today is. Sounds straightforward, right? It wasn’t. But once I broke it down into steps, it all came together.

Here’s how I approached it.


The Goal: Always Show Last Week

Let’s say today is Sunday the 19th.

What I want is:

  • Start of last week → Sunday the 12th 

  • End of last week → Saturday the 18th 

And I want this to work dynamically, no matter what day today is—Tuesday, Friday, whatever. Can Knack do that?


Step 1: Get Today’s Date

First things first, I created an equation field to grab today’s date:

  • currentDate() 

This gives me the current date automatically. Nothing fancy here, but it’s the foundation for everything else.


Step 2: Get the Day of the Week (as a Number)

Next, I needed to know what day of the week today is—but as a number, not text.

In Knack, I used:

  • getDateDayOfWeek({Current Date (Today)}-(Date Day of Week}-1)

This returns:

  • Sunday = 1

  • Monday = 2

  • Tuesday = 3

  • …and so on


This numeric value is critical because we’re going to use it for date math.


Step 3: Find the Start of the Current Week

Now we start getting into the logic.

To find the start of the current week (Sunday), I subtract the day-of-week value (minus one) from today:

  • {Current Date (Today)}-({Date day of Week}-1)


Why this works:

  • If today is Sunday (1):→ subtract 0 → stays the same

  • If today is Tuesday (3):→ subtract 2 → lands on Sunday

So no matter what day it is, this formula always takes me back to the most recent Sunday.


Step 4: Get the Start of the Previous Week

Once I have the start of this week, getting last week is easy:

  • (Start of Current Week) - 7 

So now I’ve landed on the previous Sunday.


Step 5: Get the End of the Previous Week

To get the end of last week (Saturday), I just add 6 days:

  • (Start of Previous Week) + 6 

Important:Don’t add 7—you’ll overshoot into the next Sunday. Since we’re already including the start day, adding 6 gets us exactly to Saturday.


Step 6: Combine It Into a Clean Output

Finally, I created a text formula to display something like:

“Previous week: April 12 through April 18”

This pulls together:

  • Start of previous week

  • End of previous week

And presents it in a user-friendly way.


Why I Broke It Into Multiple Fields

Could I have crammed all of this into one giant formula?

Absolutely.

But I didn’t.

Instead, I created separate equation fields for:

  • Today’s date

  • Day of the week

  • Start of current week

  • Start of previous week

  • End of previous week

Why? Because it makes debugging way easier. If something breaks, I can see exactly where the issue is.


Final Thoughts

What seems like a simple request—“just show me last week”—actually requires a few layers of logic in Knack.

But once you understand:

  • How DAYOFWEEK() works

  • How to anchor to the start of the week

  • And how to shift backward

…it becomes a repeatable pattern you can use anywhere.

If you’re building dashboards, reports, or automations, this is a really handy trick to have in your toolbox.


If you’ve got questions or want help adapting this to your app, let me know—happy to help.



Interested in my Knack database services? ... Book a call with me here: https://calendly.com/daveparrish/callwithdave 






 
 
 

Comments

bottom of page