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

[JonathanBowen]

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

Airtable - Support

Formulas and date fields

If you would like to incorporate the data from a date-type field into a formula field, the best way to do this is by using the DATETIME_FORMAT function. (For more information on the different funct...

JB

[CottageKeeper]

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!

[Justin_Barrett · Accepted solution]

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.

[CottageKeeper]

Justin_Barrett:

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

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

[CottageKeeper]

Well I’ve run into another issue.
Based on the date sold, this is clearly not week 1. Here are some screenshots, would greatly appreciate any insight on how to fix this without going back to my initial problem posted over a year ago.

I created a Week Test column to see if this would yield a different result tried two different formulas one agreed with the other formula and then changed the w to a W and it return 53 weeks which there is no such thing.

Many thanks! :smiling_face_with_three_hearts:

[Justin_Barrett]

CottageKeeper:

Based on the date sold, this is clearly not week 1.

Actually, it is. Whatever week contains January 1 is the first week of any year. If December 31 of the previous year falls in the same calendar week as January 1, then that last partial week of December is technically the first week of the next year.

CottageKeeper:

I created a Week Test column to see if this would yield a different result tried two different formulas one agreed with the other formula and then changed the w to a W and it return 53 weeks which there is no such thing.

Actually, there is. :slightly_smiling_face: The “W” specifier returns the ISO week number, and according to the docs that can range from 1 to 53 (technically the “w” specifier also allows 53, but it’s using a different calculation than the ISO version).

Airtable Support

Supported format specifiers for DATETIME_FORMAT

The DATETIME_FORMAT function will allow you to reformat the data from the date-type field into a string of your specifications. This is written in the form "DATETIME_FORMAT(Datetime, 'format s...

[CottageKeeper]

Thanks for the reply Justin. So I’ve learned quite a bit. The differences are not explained clearly on Airtables ‘Supported format specifiers for DATETIME_FORMAT’ web page.

Here are 5 columns with 5 different formulas:

This formula in the WK Sold (A) “appears” to start the week on Sunday and end on Saturday:

IF({Date Sold/Ended}=0, BLANK(), WEEKNUM(SET_TIMEZONE({Date Sold/Ended}, “America/New_York”)))

These two formulas below “appear” to start the week on Monday and ending on Sunday. Which is a bit confusing because this would mean both ‘w’ and ‘W’ are ISO which is a calendar week that starts on Monday and ends on Sunday. But the Airtable Supported format specifiers for DATETIME_FORMAT webpage don’t indicate this.

WK Sold (B)

IF({Date Sold/Ended}=0, BLANK(), WEEKNUM(SET_TIMEZONE({Date Sold/Ended}, ‘W’,“America/New_York”)))

WK Sold ©

IF({Date Sold/Ended}=0, BLANK(), WEEKNUM(SET_TIMEZONE({Date Sold/Ended}, ‘w’,“America/New_York”)))

To check and confirm the two above formulas were ISO I created two more columns with formulas that don’t use the WEEKNUM and SET_TIMEZONE. The results are quite confusing. For example 12/31/20 results in week 1 in all columns except column (D), which would make me think all the columns BUT (D) would give the same results but if you look at 1/16/21 this is not the case. So I’m confused, as to why all these columns sometimes agree and sometimes don’t agree. It has to be with the start day of the week. It’s clear what is a start day of Sunday in (E) and should be in (A) and © isn’t the truth.

WK Sold (D)

IF({Date Sold/Ended}=0,BLANK(), DATETIME_FORMAT({Date Sold/Ended},‘W’))

WK Sold (E)

IF({Date Sold/Ended}=0,BLANK(), DATETIME_FORMAT({Date Sold/Ended},‘w’))

I just really need to know which formula to use where the week starts on Sunday and ends on Saturday. My brain is utterly confused… :crazy_face: :woozy_face:

Thanks in advance for your brilliant insight!! :pray:

[Justin_Barrett]

CottageKeeper:

I just really need to know which formula to use where the week starts on Sunday and ends on Monday.

I think you mean “starts on Sunday and ends on Saturday,” correct?

I’ll try to dive into this later, but it probably won’t be until Monday. However, I can offer a small simplification to your formulas in the meantime. Instead of starting them like this:

IF({Date Sold/Ended}=0,BLANK(), ...

Switch to this:

IF({Date Sold/Ended}, ...

Comparing a date field against a number (0) isn’t the best because you’re not comparing like values (dates aren’t stored as numbers). The latter example above is cleaner for two reasons:

• A non-empty field always equates to true (except for number fields, when a 0 will still be seen as false, but that’s a different issue)
• The third argument in the IF() function is optional. If omitted, Airtable automatically returns the proper BLANK()-equivalent value based on the output of the second argument.

[CottageKeeper]

Yes, Sunday to Saturday! :woozy_face: I have edited my comment above. Thanks for pointing that out.

I changed all the formulas as you requested and checked to see if it changed any of the results and it did not.

Grateful for any insight. :pray: