Help

Re: DATETIME_FORMAT Formula Week # Incorrect

Solved
Jump to Solution
1479 1
cancel
Showing results for 
Search instead for 
Did you mean: 
CottageKeeper
6 - Interface Innovator
6 - Interface Innovator

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
Screen Shot 3 2019-08-14 at 12.59.55 PM.png

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

Screen Shot 2 2019-08-14 at 12.59.27 PM.png

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

1 Solution

Accepted Solutions

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.

See Solution in Thread

10 Replies 10

Hi @CottageKeeper - 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’?

Screen Shot 2019-08-14 at 6.01.47 PM.png

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:

CottageKeeper
6 - Interface Innovator
6 - Interface Innovator

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:

Screen Shot 2021-01-21 at 10.11.35 AM

Screen Shot 2021-01-21 at 10.11.56 AM

Screen Shot 2021-01-21 at 10.30.32 AM

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.

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).

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:

Screen Shot 2021-01-22 at 10.43.42 AM

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:

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.

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:

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}))