Hi all,
I have tried to set up a calculation field type to set a date that is 1 day before a preset date field. I have selected output type as date but the sample output seems to be set as a number and this doesnt let me check if the equation has worked and I want to see it as a date? Any chance someone can let me know how to make this a date?
Hi @Hyroxware,
That’s very strange – have you checked a record whether it also displays the number instead of a date? If so, this seems to be a bug and I’d recommend reaching out to HubSpot support.
You could also try recreating the property anew, in the hopes that this one was just a hickup in the system.
Best regards!
I never said it displayed a number instead of a date. Its displaying the incorrect date.
Hi @BCanarecci (and tagging @karstenkoehler as an additional expert here, in case he has other thoughts on this),
HubSpot stores Date properties as “Midnight UTC.”
Your calculation adds exactly 320 days of milliseconds to that date, resulting in a new timestamp that is exactly Midnight UTC.
In the deal name, text tokens often display the raw UTC date value (which appears correct).
But, in the property view, the user interface on the record converts that UTC timestamp to your local time.
Because the time falls back into the previous evening, the property displays the date as one day early.
To fix this in a Custom Equation, you should force the resulting time to be in the middle of the day (e.g., Noon UTC) rather than Midnight UTC.
Then, even if the timezone shifts back 5 or 8 hours, it is still the same calendar day.
I recommend adding 12 hours worth of milliseconds to your formula instead.
Ex: … + ((((320 * 24) * 60) * 60) * 1000) + 43200000)
I also noticed your formula ends with , 0). if(…, …, 0)
Be careful with this! In Unix time, 0 equals January 1, 1970.
If a record does not have “AEP” as the utility company, their date will be set to 1970.
Hope this helps!
