DATETIME_FORMAT Formula Week # Incorrect

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.

07%20PM

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

Hi @Lisa_Morales - I’m pretty sure this is something to do with timezones, take a look at this help article (at the bottom):

JB

Thanks, Jonathan very familiar with this page but previously it didn’t seem to offer any solution. I had to create another field (Timezone for Date Sold) :neutral_face: and use a formula that references the Date Sold field and used the datetime_format and set_timezone to get the correct date and timezone and then adjusted the formula in the WK Sold from the Date Sold field to the new field (Timezone for Date Sold) and now the weeks are calculating correctly. Was hoping to avoid having to add another field, but this is the only way I can get it to work. I’ll keep the (Timezone for Date Sold) field hidden to avoid confusion.

Is there a way to enter the Date Sold in my timezone ‘America/New_York’?

Thank you!

Unfortunately some Airtable datetime formulas operate on retrieved dates as though they’re based on GMT, even if you don’t have the GMT switch turned on for that field. An alternate way to keep this a little cleaner is to use SET_TIMEZONE inside the WEEKNUM function that you’re using in the {WK Sold} field:

WEEKNUM(SET_TIMEZONE({Date Sold}, "America/New_York"))

That way you can nix the extra field.

Thank you, Justin! This worked Brilliantly!!! :slightly_smiling_face: