Date Difference issue

[jjustingkm]

I am using the below formula to get the date difference between current date and previous month end. But getting incorrect result. looks like the issue is because of $(vMonthEnd) value, when I check this variable value in the script editor, get the correct value (4/30/21), but in the sheet value looks like 6.9657 something. Any help would be appreciated.

$(vMonthEnd)  - currentdate() 

vMonthEnd=date(floor(monthend(addmonths(Today(),-1))), 'MM/DD/YYYY') - now it is 4/30/2021.

[Gysbert_Wassenaar · Accepted solution]

The problem is that you format the value as a date with format MM/DD/YYYY. If you then dollar expand that value it calculates the result of 4/30/2021, in other words 4 dived by 30 divided by 2021.

Try leaving out the Date() function: floor(monthend(Today(),-1))

[jjustingkm]

Thanks for the quick response. that worked. Just a quick question, 4/3021 shows as 44316, is this Julian or something?

[Gysbert_Wassenaar]

44316 is the number of days since 12/31/1899. It's how lots of computer systems store dates.