Make a negative number to a zero using DATEIF function

[EmmaTM]

Hi,
I want to take the number of days between 2 dates however when this is a negative number I want that to result in a zero.
The figures in cells L13 & L14 were both negative but I want these to be zero.
The formula I have used is:
=DATEDIF(IF(E13<$M$1,E13,$M$1+1),IF(E13>$M$1,E13,$M$1+1),"d")

Thank you

 

[S Oberlander]

Try =IF($M$1-E13<0,0,$M$1-E13)

 

[Eric W]

Or:

=MAX(0,$M$1-E13)

 

[EmmaTM]

Perfect, thank you

 

[EmmaTM]

See cell L9 above which is showing 104 days, this should be 92 days.
I am trying to work out the number of days from the start date to the month end date so in this case its 19/8/21 to 30/11/21 which should be 92 days.

 

[Sufiyan97]

But your formula calculates date based on cell M1 which is 30/11/2021
So you need to change the reference

Book2
	D	E	F	G	H	I	J	K	L	M	N
1										11/30/2021
2
3
4
5
6
7
8
9		8/19/2021	11/18/2021			104	91
10
11
Sheet1

Cell Formulas
Range	Formula
I9	I9	=DATEDIF(IF(E9<$M$1,E9,$M$1+1),IF(E9>$M$1,E9,$M$1+1),"d")
J9	J9	=F9-E9

 

[Eric W]

August 18th through November 30 is 104 days. 14 in August, 30 in September, 31 in October, 30 in November. 14+30+31+30 = 105. This is 1 higher than 104 since it counts both endpoints.

 

[EmmaTM]

Is there a formula I can use that would look at the end date and look at the month end date and tell me the number of days. For example if the month end date is 30/11/21 and then:
I have an end date of 18/11/21 I would want this to show 18 days in Nov.
I have an end date of 15/12/21 I would want this to show 30 days in Nov.

Thank you

 

[Sufiyan97]

I think you need to start a new thread for that.