Ending date for week number (getDateWeek) function

[Laurie]

I am trying to get a Week Ending date to corispond to the weekNumber this allows me to get the avg percentage of uptime for the week. In my Production Report table I created a formula field to get the week number based of of date field. Now what I need to do is make it easier on my folks running the report when selecting which week number of the year they want to look at they can select the week ending date for the week number. Is there a formula that I can have it look at either the date or week number and return the actual end date for that week?

Example date in table = 06/18/2025 which then returns weekNumber = 25 I would actually like it to display 06/22/2025. our weeks end on Sunday.

[StephenChapman]

Hi @Laurie,
You can use this method to calculate a date’s following Sunday:

1. Add an equation field called Day of week, with ‘Numeric’ equation type, and the following equation:

getDateDayOfWeek({Start Date})

2. Add a second equation field called Week end date, with ‘Date’ equation type, ‘Days’ date type, and ‘Date’ result type, and the following equation:

{Day of week} == 1 ? {Start Date} : {Start Date} + (8 - {Day of week})

Your output should look something like this:

The reason we can’t do it all in one equation is because Knack’s equations don’t know how to calculate both a number and date value at the same time.

Does that help?

[Mathew]

I’m so happy this question was added and answered. The formula and solution solved a problem I have been working on.

[CarlHolmes]

Nice one @StephenChapman - your brain works in magical ways