cancel
Showing results for
Did you mean:

## date difference calculation with positive and negative number

Hi All,

Currently i need to calculate 2 dates difference with Postive & negative numbers.

i need to have both positive and Negative numbers in calculation. when i use Datediff formula, i always have positive numbers.

how to get negative numbers in date difference calculation.

Example

Current date: 1/22/2019

Start date: 12/31/2018

Then value should be 22 and if

Current date: 1/22/2019

Start Date: 2/22/2019

Then value shoule be -30.

1 ACCEPTED SOLUTION
Community Support

Hi @sandokpn,

To create a calculated column as below.

```datediff = var diff = DATEDIFF('Table1'[Start date],'Table1'[Current date],DAY)
var diff2 = DATEDIFF('Table1'[Current date],Table1[Start date],DAY)
return
IF(Table1[Start date]<='Table1'[Current date],diff,0-diff2)
```

For more details, please check the pbix as attached.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
5 REPLIES 5
Community Support

Hi @sandokpn,

To create a calculated column as below.

```datediff = var diff = DATEDIFF('Table1'[Start date],'Table1'[Current date],DAY)
var diff2 = DATEDIFF('Table1'[Current date],Table1[Start date],DAY)
return
IF(Table1[Start date]<='Table1'[Current date],diff,0-diff2)
```

For more details, please check the pbix as attached.

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Hello,

Wondering if we need two variable declarations for this purpose.  Datediff provides the difference between the two dates defined - it doens't matter the order in the function.  The code below worked just fine for me.

Regards,

Matthew

Community Support

Hi @sandokpn,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Community Champion

try just subtracting them, e.g.

`Table[Current date] - Table[Start date]`

you may need to format it as a whole number, and possibly round if you have times included (they're represented as fractions), but other than that should work fine

Thank you for the kudos 🙂

Proud to be a Super User!

I have tried that, but that is not working.

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.