Calculate difference between 2 dates in days

[DPM_reports]

I have dates in 2 columns, I want to calculate the difference the two in days…how do I achieve this.

[WillowMobileSys · Accepted solution]

There is a TOTALHOURS() function I believe you could use.

TOTALHOURS([Date2] - [Date1]) / 24

A feature request has been made for a TOTALDAYS() function but it is not yet available as far as I know.

[DPM_reports]

Hi, could u please also tell me what is column type I should have while I use this expression provided

[Koichi_Tsuji]

Try duration type.

[DPM_reports]

with type set to duration it showed as error…when i changed it to Number Type…it worked

[WillowMobileSys]

Decimal is the best type. The resulting value is in hours but could have partial hours such as 124.8.

Using Number is okay if you are good with rounded values to the nearest whole day. I believe 124.8 would be rounded up to 125 while 124.3 would be rounded down to 124. At least I believe that is what I have observed when a decimal value is stored into a Number type column. If the rounding is of concern, you can easily test what happens.

[proyectotesis22]

hey do you know how to that but instead of days is weeks

[SkrOYC]

Changing "24" to "168" or adding "/7" at the end, after wraping the previous with "()"

[admin_YyH]

Instead, I would prefer to do something like

WEEKNUM([Date 1])-WEEKNUM([Date 2])

[Suvrutt_Gurjar]

---

@admin_YyH wrote:

Instead, I would prefer to do something like

WEEKNUM([Date 1])-WEEKNUM([Date 2])

---

That is a good option as long as the both the dates are in the same calendar year.  If the two dates are in different calendar years , the expression may not work as the WEEKNUM() resets to 1 in every new year's first week.