2 followers Follow

Unexpected date to number calculation

Hi there,

I am trying to do some calculations based on month and date. Unfortunately the system is calculating every 1st day of the month as the last day of the previous month.

01/01/2021 would be calculated in numbers as 31 12 2020. Another example would be 01/02/2021 as 31 01 2021. 

Calculating 02/01/2021 would result into 01 01 2021.

it would always calculate 1 day in the past.

When I use default functions to do a calculation and the results end-up wrong. Support said it's not a bug but have not yet explain to me why the following is happening:
- MONTH(01/01/2021) would result into the number 12.
- DAY(01/01/2021) would result into the number 31.
- YEAR(01/01/2021) would result into the number 2020. -> only when its the 1st of january.

How does this happen and how can this be prevented because making quarterly reports is not doable now.


thanks in advance

Yari Bernardus Completed

Official comment


Hi Yari,

It's probably due to timezone conversions. Please try using ToOrganizationalDatetime() function. For example, ToOrganizationalDatetime(Date(2021, 1, 1))

I hope this helps.


Roland Pumputis
Comment actions Permalink

Please sign in to leave a comment.

1 comment