Calculate a Monday date

[Matt224255376]

I have an app where time is entered daily with a report showing weekly subtotals. This works fine using "getDateWeek({Date_of_time_entry })".

However the totals shown in the report are only by reference to week number - e.g. "Week 22: 55 hours".

This is a bit unintuitive. If possible, I would like this to be set with reference to "week starting" (week ending will also be fine). e.g. "Week starting 2020-05-25: 55 hours".

I know this is possible using Excel and various formula, but all those options rely on turning a date into a sequential number, manipulating it, then turning it back in to a date. I can turn the date into a sequential number in Knack using this modulus method (https://support.knack.com/hc/en-us/community/posts/220702188-How-to-tell-what-day-of-the-week-a-given-date-is), but cannot see how to turn it back into a date.

I have tried both "Monday-ising" a particular date code, and trying to work out the relevant Monday of a given week code, but haven't had any success.

Any ideas greatly appreciated.

[Calvin_B]

@Matt224255376 Did you come up with a solution to this? I have the same need at the moment.

[Matt8]

No, never managed a proper solution unfortunately

[Dean2]

Hi

Here’s a quick and dirty “proof of concept” that this should be achievable.

Or, maybe.

This would have been so much easier to achieve if there was a function like the Excel’s DATE function.