DATETIME_FORMAT help please

[Hayley_Devlin]

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

[ProsperSpark]

Hello Hayley,

Here is one solution.  Try adding a new formula column to your table with this formula:  

IF(
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!