DateDiff to return positive and negative numbers

[marcus314]

Hi,

I searched for a solution, but couldn't find one that fit my specific needs, so I thought I'd turn to this great forum!

I have a date in cell M2 and am wishing to calculate the date difference in days. The formula I created works great when there is a positive difference. However, for anything < 0, the cell is blank. I'm essentially trying to show both positive and negative values.

=IFERROR(DATEDIF(TODAY(),M2,"d"),"")

Any ideas how to adjust this formula to achieve this?

Thanks!

 

[sandy666]

maybe

=IFERROR(DATEDIF(TODAY(),M2,"d"),-DATEDIF(M2,TODAY(),"d"))

define which part should show negative or positive value

 

[JoeMo]

Here's one way:

=IFERROR(DATEDIF(TODAY(),M2,"d"),IFERROR(-DATEDIF(M2,TODAY(),"d"),""))

DATEDIF requires the first date argument to be less than or equal to the 2nd date argument.

 

[Fluff]

How about

=M2-TODAY()

and format the cell as general.

 

[Dossfm0q]

feed back please

المصنف1
	K	L
1	1/28/2021	50
2	12/19/2020	10
3	10/20/2020	-50
4	10/20/2020	-50
ورقة1

Cell Formulas
Range	Formula
K1	K1	=TODAY()+50
L1:L4	L1	=IFERROR(DATEDIF(TODAY(),K1,"d"),-1*DATEDIF(K1,TODAY(),"d"))
K2	K2	=TODAY()+10
K3:K4	K3	=TODAY()-50

 

[Dave Patton]

T202002a.xlsm
	K	L	M
7
8	22	9-Dec-20	31-Dec-20
9	22	9-Dec-20	31-Dec-20
10	22		31-Dec-20
11	8	9-Dec-20	1-Dec-20
12	8		1-Dec-20
13	8	9-Dec-20	1-Dec-20
14	8	1-Dec-20	9-Dec-20
15	8		1-Dec-20
6e

Cell Formulas
Range	Formula
L13,L11,L8:L9	L8	=TODAY()
K8	K8	=DATEDIF(L8,M8,"d")
K9	K9	=DATEDIF(MIN(L9,M9),MAX(L9,M9),"d")
K10:K12	K10	=DATEDIF(MIN(TODAY(),M10),MAX(TODAY(),M10),"d")
K13:K14	K13	=ABS(L13-M13)
K15	K15	=ABS(TODAY()-M15)

 

[marcus314]

Thanks everyone. Every solution worked great. I appreciate the amazing community here for all of the help!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[sandy666]

You are welcome
Have a nice day

 

[sandy666]

another option

		Result		Example
Date		Days		Date	Today	Days
05/12/2020		-5		05/12/2020	10/12/2020	-5
20/12/2020		9		20/12/2020	10/12/2020	9

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DTLN = Table.AddColumn(Source, "Today", each DateTime.LocalNow()),
    Days = Table.AddColumn(DTLN, "Days", each Duration.Days([Date] - [Today]), Int64.Type),
    TSC = Table.SelectColumns(Days,{"Days"})
in
    TSC