Incorrect date and time

[John_B2]

It's currently 19:55 on the 21st december in New Zealand. Why does this calculation return a date of the 20th december and the time as 18:55? Surely it can't be that hard to show the correct date for the country you're in?

[kuovonne]

Dates and times in formula fields are GMT time, unless you specify otherwise. 

SET_TIMEZONE() by itself does not work unless you nest it inside DATETIME_FORMAT(), MONTH(), YEAR(), etc. 

Instead, use NOW() by itself and set the format options for the formula to specify the “local time”. Not that the time is still likely to be off because NOW() does not update continuously. At best, it updates every 5 minutes or so, but it can also be several hours off. 

[John_B2]

Thanks Kuovonne. NOW() would have been the first thing I tried. It doesn't work as it always includes a time and I'm needing to compare dates without the time. My imported dates all have 00:00:00 for time so when comparing them with NOW() or TODAY(), the calculation must first convert the date to the correct timezone and then replace the time with zeros. Hence the following calc :

DATEADD(DATETIME_PARSE(YEAR(NOWNZ) & "-" & MONTH(NOWNZ) & "-" & DAY(NOWNZ) & "T00:00:00.000Z"),-13,"h")

where the NOWNZ field is just NOW() and displayed as NZ timezone. I have to do it this was as using NOW() in the calc would of course return GMT time!

Phew! Makes me miss Filemaker!