
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Nov 08, 2023 01:30 PM
Hi all,
I want to use a formula to group my outgoing payments by week. The tricky part is that I want to start the week on Thursday (so it runs Thursday to Wednesday). Can anyone help me with a formula for that please?
TIA, H
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Nov 10, 2023 05:36 PM
Hello Hayley,
Here is one solution. Try adding a new formula column to your table with this formula:
WEEKDAY({Your date field}) >= 4,
WEEKNUM({Your date field}),
WEEKNUM(DATEADD({Your date field}, -1, 'days'))
)
This will assign the week of the year to each row. Like this:
Here is how it works:
Thursday or Later?
- If your "Dates" field lands on Thursday or a later day, it's part of the current week. The formula leverages WEEKNUM() to extract the week number.
Before Thursday?
- No need for concern. If it's Wednesday or earlier, the formula adjusts by subtracting a day (DATEADD(Dates, -1, 'days')) before determining the week number.
In essence, this formula computes week numbers, aligning with a custom week structure beginning on Thursday and concluding on Wednesday. It ensures clarity without involving time details in the "Dates" field.
Should you have any questions or require additional clarification, feel free to reach out and we'll be happy to help! www.prosperspark.com
Take care!
