Duration formula that returns "0" If there are blank fields

[Jalayne]

I'm trying to create a "duration" Column from a spreadsheet that allows for a returning a "0" if both "start date" and "end date" columns are blank. So far I've got the following which calculates duration and also returns a value using the current date if "End Date" is empty, so i just need to know how to add to this formula to get a "0" if there no date in either column:

=IF(ISBLANK([@[PoP End]]),TODAY()-[@[PoP Start]],[@[PoP End]]-[@[PoP Start]])

Thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF([@[PoP Start]]="",0,IF([@[PoP End]]="",TODAY()-[@[PoP Start]],[@[PoP End]]-[@[PoP Start]]))

 

[Toadstool]

Hi Jalayne,

I've only Excel 2016 so I had to repove the spaces from the column names, but otherwise this should work:

Jalayne.xlsx
	A	B	C
1	PopStart	PoPEnd	Result
2	1/1/2021	1/23/2021	22
3			0
4	1/1/2021		0
5			0
6	1/1/2021	2/2/2021	32
7	1/1/2021	3/3/2021	61
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=IF(AND(ISBLANK([@PoPEnd]),ISBLANK([@PoPEnd])),0,IF(ISBLANK([@PoPEnd]),TODAY()-[@PopStart],[@PoPEnd]-[@PopStart]))

Named Ranges
Name	Refers To	Cells
PoPEnd	=Sheet1!$B$2:$B$7	C2
PopStart	=Sheet1!$A$2:$A$7	C2

 

[Jalayne]

Hi & welcome to MrExcel.
How about

=IF([@[PoP Start]]="",0,IF([@[PoP End]]="",TODAY()-[@[PoP Start]],[@[PoP End]]-[@[PoP Start]]))

That's it! Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.