Question

Hi,

I have put together a calendar database comprised of types for days, weeks, months, etc. For the purpose of a naming formula, I was wondering if there was a way to find the beginning and end dates of a week from any date within a week?

For example, today is Tuesday August 17, 2021, which falls in the 34th week of this year. I want to try and make a formula whose output will be the first and last dates of the week. In this case, I would want the output to read August 15, 2021 - August 21, 2021.

Thanks in advance and all the best!

Answer 1

After some tinkering around with formulas, I found this was possible with the help of dateSubtract and dateAdd functions. I used this to create a function for a large calendar with types for days, weeks, months, quarters, and years that I could use to connect days to weeks. For the below example, you need a database with date property and a formatDate function of "dddd", but you could also find other ways to make this work.

if(prop("Weekday (dddd)") == "Sunday", concat("('", formatDate(prop("Date(s)"), "YY"), " #", formatDate(prop("Date(s)"), "w"), ") ", formatDate(dateSubtract(prop("Date(s)"), 0, "days"), "DD.MM"), " - ", formatDate(dateAdd(prop("Date(s)"), 6, "days"), "DD.MM"))), "")

The outcome: ('YY #w) DD.MM - DD.MM

For ever consecutive day, add 1 to the dateSubtract function and subtract 1 from the dateAdd function.

Hope this helps someone else!