DATETIME_FORMAT Formula Week # Incorrect

[CottageKeeper]

Hello Friends - I created a formula to return the Week # an Item Sold, it was working great until this past week. In the screenshot below the second item in the list sold on August 10, 2019, at 9:01 pm. But the formula in the Week Sold field is returning that the item sold in Week 33 when it is actually Week 32.

Here is the formula I’m using for the Week Sold field:
DATETIME_FORMAT({Start Date},‘w’)

Need your help, I think this is due to the fact that I have hours in my Date Sold field, but I’m not certain how to proceed with correcting the formula.

Thanks in advance for your support!!!

[Justin_Barrett]

Sorry for the delay in looking at this. Unfortunately I’m not in a position to do a deep dive into this issue, and I probably won’t be in such a position for at least another month, possibly longer. On top of that, I don’t envision hitting a “resolution” to this issue (in quotes because I’m not sure if it’s even possible to resolve this completely) without at least an hour or two of messing around, and unfortunately I’m not sure that I can justify that time.

The main problem that I see is that if the numbers coming from one calculation are fudged to get what you want when transitioning from one year to the next, then that ends up potentially throwing off the calculations at the end of that year into the following year. In other words, “fixing” the problem on one end just makes bigger problems on the other, and it gets worse over time.

Because it appears that no system is going to hit your vision of ideal, my recommendation is that you just pick one system and stick with it, flaws and all.

As one final side note, the SET_TIMEZONE() function is only designed to work in conjunction with DATETIME_FORMAT(). If you go with one of the options that uses the WEEKNUM() function, then you can drop the timezone calculation as it’s not actually going to change anything about the date in question. This alone will do the trick:

IF({Date Sold/Ended}, WEEKNUM({Date Sold/Ended}))