Need help resolving discrepancy between days between function and excel calculation

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
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
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