I have a problem with getting a formula to deal with the difference in GMT and NZDT

Heather C

I have tried to add a date only column =DATEONLY(Created@row) that feeds off the Created column, but am finding that any entry prior to 1pm is showing the previous day's date. We are GMT + 13 at the moment in New Zealand as it is Daylight Savings but would normally be GMT + 12.

Date Problem.png

I am not that good with formulas so any help would be appreciated, thank you.

KPH

Hi @Heather C

The DATEONLY function will convert and display time based on GMT, not the account’s time zone settings. If you use DATEONLY on a System Date column, the date produced by the function may be different from your local time zone. 

So, if you are not working in GMT, this isn't going to work easily for you. But, there is a way...

As you have identified that it goes awry at 1pm you could create a formula to add hours if the created date timestamp is after 1pm. But that does not solve the issue with Daylight Savings happening in NZ at a different time to the UK.

My suggestion is not to use that function at all.

You can make your own. You need these parts. I am using VALUE rather than extracting MONTH and DAY to allow for this to work with different date formats. For dates in dd mm yyyy format (which I am not, so I can't test), I think you need:

Day

=VALUE(LEFT(Created@row + "", 2))

Month

=VALUE(MID(Created@row + "", 4, 2))

Year

=YEAR(Created@row)

You don't need to make all those columns - I've separated them out so you can see how the formula is built).

You combine the three parts in one formula to create the date only yourself (in a column that is date type) :

=DATE(YEAR(Created@row), VALUE(MID(Created@row + "", 4, 2)), VALUE(LEFT(Created@row + "", 2)))

KPH

Hi @Heather C

The DATEONLY function will convert and display time based on GMT, not the account’s time zone settings. If you use DATEONLY on a System Date column, the date produced by the function may be different from your local time zone. 

So, if you are not working in GMT, this isn't going to work easily for you. But, there is a way...

As you have identified that it goes awry at 1pm you could create a formula to add hours if the created date timestamp is after 1pm. But that does not solve the issue with Daylight Savings happening in NZ at a different time to the UK.

My suggestion is not to use that function at all.

You can make your own. You need these parts. I am using VALUE rather than extracting MONTH and DAY to allow for this to work with different date formats. For dates in dd mm yyyy format (which I am not, so I can't test), I think you need:

Day

=VALUE(LEFT(Created@row + "", 2))

Month

=VALUE(MID(Created@row + "", 4, 2))

Year

=YEAR(Created@row)

You don't need to make all those columns - I've separated them out so you can see how the formula is built).

You combine the three parts in one formula to create the date only yourself (in a column that is date type) :

=DATE(YEAR(Created@row), VALUE(MID(Created@row + "", 4, 2)), VALUE(LEFT(Created@row + "", 2)))

Heather C

Hi @KPH,

That works perfectly, thank you so much! You are a life saver, and explaining it all so that I can see what you have done and how it works is extremely helpful. Thank you again, have a great week!

KPH

Glad to have helped @Heather C and thank you for the ❤️.