0

A data grid I'm working on contains two dates and a calculation result, a start date, an end date and the number of days between them. the calculation between them is represented by the formula: daysBetween(trimToDaysUTC($StartDate),trimToDaysUTC($EndDate)) for example start date: 1/8/2024 and end date: 5/24/2024 has the result 136.00 But when I export the data grid to excel and subtract the dates '=B2-B1' I get the value 137.00. this is an inconsistent problem as some sets of dates will get the same value as that calculated in excel but others wont. The value difference is never greater then 1. What could be causing the issue and how do I fix it?

asked

Landen Lefrancois

2 answers

2

Hi Landen Lefrancois

UTC functions use the UTC calendar., and `datetime functions `

uses the user calendar.

I think this is not an issue you need to distinguish between UTC and user calendar

try to use parseDateTime function inside the daysBetwwen function

also you can see this to understand more about date time functions

**Please recommend this as accepted answer if it answers your question**

Thank you

answered

Loai AbuKhil

0

You've not said where you are in the world, but it sounds like you are in a country that changes it's clocks for daylight saving. The dates you have given cross the DST boundary, so the UTC time between the two dates won't round up to a complete day.

If the dates in your Mendix application are localised then use trimToDays instead of trimToDaysUTC in your calculation.

Good luck!

answered

Robert Price