There is a nice formula in SuiteAnswers which provides the number of days between two dates. The problem for me is that in the user interface we are entering dates in DD/MM/YYYY format and the formula assumes all dates are MM/DD/YYYY.
Here is the formula that I am using to set a field via Workflow.
(Date.parse({custbody_billing_period_to.id})-Date.parse({custbody_billing_period_from.id}))/86400000+1
So If I enter the following dates
5/4/2020 - 5/4/2020 that returns 1
increment by one Australian day
5/4/2020 - 6/4/2020 that returns 32 days
5 April - 6 April should be 1
it is interpreted as 4 May - 4 June= 32
I tried a few versions without success like
(Date.parse(TO_DATE({custbody_billing_period_to}))-Date.parse(TO_DATE({custbody_billing_period_from})))/86400000+1
(Date.parse(TO_CHAR({custbody_billing_period_to},'DD/MM/YYYY'))-Date.parse(TO_CHAR({custbody_billing_period_from},'DD/MM/YYYY')))/86400000+1
(Date.parse(TO_CHAR({custbody_billing_period_to},'MM/DD/YYYY'))-Date.parse(TO_CHAR({custbody_billing_period_from},'MM/DD/YYYY')))/86400000+1
Any tips on getting that date recognised in the right time zone?