Datetime_format() showing wrong day

[Hunter_Reynolds]

Hi. I’m trying to use DATETIME_Format() inside of a formula field to display the day in which an order was placed.

These are the dates of the orders. For reference, the 18th took place on a Sunday.

The formula block is a very simple DATE_FORMAT() function, showing the day. However, it seems as if the date format is incorrect and is defaulting to GMT time (adding 4 hours to the time fields, causing the time to roll over to the next day). I don’t know how to fix this, and I can’t change it because under formatting it says:

Can someone please help?! Thanks

[Jeremy_Oglesby]

Unless you are collaborating with somebody in another time zone who absolutely needs to see those dates relative to their own time zone, the easiest way to deal with this is to open up the field settings on that DateTime field for when the order was placed and select that “Use the same time zone…” option.

[Hunter_Reynolds]

Thanks for your reply! I tried turning on GMT, but then it changed all of my “Created Time” fields and now new records are not showing the correct time as you mentioned might happen. We need to know what time an order was placed in the club. If the order was placed at 9:30pm, it shows “1:30am” with GMT turned on… not ideal.

Ultimately, we’re trying to get to the same date so that I can show the day in which an order was placed. Since an orders deadline is created based on the day in which it was purchased, we need to know the correct day. Since the clubs are open until 3:30am, any orders placed after 12:00am will show the day of purchase as the NEXT day, when in-fact they were placed the day before (ex. Saturday at 3:30 in the morning is showing as Sunday - we want the day to say SATURDAY). I thought that this formula would work, but it’s not giving me the intended results.

IF(AND(IS_AFTER({Proper Date Created},"12:00am"),IS_BEFORE({Proper Date Created},"8:00am")),DATETIME_FORMAT(DATEADD({Proper Date Created},-1,'day'),"dddd"),DATETIME_FORMAT({Proper Date Created},"dddd"))

I really appreciate your help.

[kuovonne · Accepted solution]

When you use DATETIME_FORMAT, the result is a string, not a date object. That is why you cannot set formatting for the formula.

Since you want to display only the day of the week, you need to show a string, not a date object.

Dates are always stored internally as GMT time, no matter what display settings you use, and thus date/time calculations that result in strings show GMT time, unless you tell it to use a different time zone.

You can set the timezone with DATETIME_FORMAT. Use the time zone specifier for your time zone.

DATETIME_FORMAT(SET_TIMEZONE({Proper Date Created}, 'Australia/Sydney'), 'dddd')

[ScottWorld]

As @kuovonne mentioned above, using the SET_TIMEZONE function will likely solve your problem.

Additionally, if you’d like to do a “deep dive” into time zones, check out my sample base & training video here:

Airtable

Time Zones: Working with multiple different time zones in Airtable - Airtable...

This Airtable base solves the problem of events taking place in different time zones, and users wanting to know what time those events are taking place in...

[Hunter_Reynolds]

Kuovonne,

Thanks for the explanation! This solved my problem!

Here’s a follow-up question: Since I’m trying to create a formula that will display the proper day an order was placed (ex. an order placed on Saturday night at 3am will show as ‘Sunday’, but needs to be adjusted in the formula to show Saturday). I’m trying to use the IS_BEFORE() or IS_AFTER() logic to decide if a time is after 12am and before 5am, which on TRUE would require using DATEADD() to subtract 1 day from the date.

IF(AND(IS_AFTER({Proper Date Created},"12:00am"),IS_BEFORE({Proper Date Created},"5:00am")), DATETIME_FORMAT(DATEADD(SET_TIMEZONE({Proper Date Created}, 'America/New_York'),-1,"day"), 'dddd'),DATETIME_FORMAT(SET_TIMEZONE({Proper Date Created}, 'America/New_York'), 'dddd'))

Here’s the current formula I’m using, but it doesn’t seem to be working. Otherwise the values in the photo above should be reading ‘Saturday’, not ‘Sunday’ - correct?

Thanks again for your help!

[Hunter_Reynolds]

ScottWorld,

That’s a great resource! Thanks for sharing!

[Justin_Barrett]

Hunter_Reynolds:

I’m trying to use the IS_BEFORE() or IS_AFTER() logic to decide if a time is after 12am and before 5am, which on TRUE would require using DATEADD() to subtract 1 day from the date.

IF(AND(IS_AFTER({Proper Date Created},"12:00am"),IS_BEFORE({Proper Date Created},"5:00am")), DATETIME_FORMAT(DATEADD(SET_TIMEZONE({Proper Date Created}, 'America/New_York'),-1,"day"), 'dddd'),DATETIME_FORMAT(SET_TIMEZONE({Proper Date Created}, 'America/New_York'), 'dddd'))

The parameters passed to IS_AFTER() and IS_BEFORE() must both be datetime objects. Wrapping quotes around a time doesn’t create a valid date for comparison, which is why “12:00am” and “5:00am” aren’t working.

I suggest using the HOUR() function to get the time of the {Proper Date Created} field for comparison. Be aware, though, that you’ll need to also account for the difference between GMT and your local time when doing this. For example, I’m in the Pacific timezone. If I want to see if a time is after midnight (which is 0 when returned from the HOUR() function; and BTW, all hours returned are based on 24-hour time), I have to compare against an HOUR() value of 7 because my timezone is GMT -7. In your case, where you want to see if the hour is between midnight and 5am, and you’re in the Eastern timezone (GMT -4), the first part of that formula would look like this:

IF(AND(HOUR({Proper Date Created}) >= 4, HOUR({Proper Date Created}) <= 9), ...

On another note, the SET_TIMEZONE() function only works when used directly inside of DATETIME_FORMAT(). Your first instance is nested inside DATEADD(), which won’t work. The nesting order need to be shifted so that DATEADD() is inside SET_TIMEZONE(). In short: add the date, then set the timezone, then format.

With all that in mind, I think this formula should work:

IF(AND(HOUR({Proper Date Created}) >= 4, HOUR({Proper Date Created}) <= 9),
DATETIME_FORMAT(SET_TIMEZONE(DATEADD({Proper Date Created}, -1, "day"),
'America/New_York'), 'dddd'), DATETIME_FORMAT(SET_TIMEZONE({Proper Date Created},
'America/New_York'), 'dddd'))

[Hunter_Reynolds]

@Justin_Barrett ,

EUREKA!!! Your solution worked perfectly!

You provided quite an innovative approach to using the GMT to solve for the different time zones. But your formula got me thinking…What happens when we add an additional club from a different timezone? In its current state, all records are set to be calculated and formatted to the “America/New_York” time zone.

Justin_Barrett:

I have to compare against an HOUR() value of 7 because my timezone is GMT -7. In your case, where you want to see if the hour is between midnight and 5am, and you’re in the Eastern timezone (GMT -4)

So then I decided (thanks to the helpful resource from @ScottWorld :slightly_smiling_face: ) I should make the data dynamic, so that the formula can consider each club’s unique time zones and closing times.

Justin - If you look at the video, I’ve managed to take your formula and alongside Scott’s GMT Airtable have replaced all static values with Dynamic values specific to a club’s timezone. However, when trying to change the static “America/New_York” value inside the SET_TIMEZONE() node with the Dynamic “America/New_York” from the table, it seems to break. Any ideas?

My Dynamic Formula:

IF(AND(HOUR({Proper Date Created}) >= {GMT Midnight Time}, HOUR({Proper Date Created}) <= {GMT Club Close Hour}),
DATETIME_FORMAT(SET_TIMEZONE(DATEADD({Proper Date Created}, -1, "day"),
"{Official Time Zone of Event (from Timezone) (from What Club?)}"), 'dddd'), DATETIME_FORMAT(SET_TIMEZONE({Proper Date Created},
"{Official Time Zone of Event (from Timezone) (from What Club?)}"), 'dddd'))

We’re so close! I’m really thankful for your guys’ help. This is a super strong community!

[kuovonne]

IS_BEFORE and IS_AFTER also only compare dates, not times.

Another option is to identify a time zone that would be at midnight when it is 5am in your timezone. Then use that timezone when you SET_TIMEZONE. That way you wouldn’t have to use DATEADD.