Number of days if no end date

[bgrove]

Hi,
I need to calculate the number of days when I have the start date but I do not have the end date (meaning the project is still open).

I have two columns
Start date = 1/1/2018
End date = blank

I used =DATEDIF(A1,Today(),"d") but it does not account for cells that have End Dates in them.

Start------ End----- #Days
Date------ Date
1/1/2018- blank------ 74 (using the formula above)
1/1/2018 - 2/10/18---74 (using the formula above. It seems I need another IF statement to check for blanks then use the DATEIF formula)

Just need another's perspective and formula suggestion on this one.

Thank you,

Bev

 

[Marcílio_Lobão]

Bev, Good afternoon.

Try to use:

=IF(B1="",DATEDIF(A1,Today(),"d");"")

Is that what you want?

I hope it helps.

 

[jtakw]

Hi,

I don't use DATEDIF, here're a couple of options:

Book1
	A	B	C	D
1	Start Date	End Date	#Days
2	1/1/2018		74	74
3	1/1/2018	2/10/2018	40	40
Sheet22

Cell Formulas
Range	Formula
C2	=IF(B2="",TODAY()-A2,B2-A2)
D2	=IF(B2="",DAYS(TODAY(),A2),DAYS(B2,A2))

Formulas copied down. Columns C and D formatted General or Number.

 

[bgrove]

Hi,

I don't use DATEDIF, here're a couple of options:

	A	B	C	D
Start Date	End Date	#Days

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]74[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]2/10/2018[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]

</tbody>

Sheet22​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(B2="",TODAY()-A2,B2-A2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(B2="",DAYS(TODAY(),A2),DAYS(B2,A2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formulas copied down. Columns C and D formatted General or Number.

Yes, this works!!! Thank you.......However as I progressed through the data I need another If statement that says "Not Started" when there is not a start date or an end date. Which means the project has not began yet. Any suggestions on what additional formula's I can add?

 

[jtakw]

You're welcome, you didn't say which formula you went with, so I modified both:

Book1
	A	B	C	D
1	Start Date	End Date	#Days
2	1/1/2018		74	74
3	1/1/2018	2/10/2018	40	40
4			Not Started	Not Started
5	1/5/2018	3/14/2018	68	68
Sheet22

Cell Formulas
Range	Formula
C2	=IF(A2="","Not Started",IF(B2="",TODAY()-A2,B2-A2))
D2	=IF(A2="","Not Started",IF(B2="",DAYS(TODAY(),A2),DAYS(B2,A2)))

Is it possible to have an End Date, but no Start Date?

 

[bgrove]

You're welcome, you didn't say which formula you went with, so I modified both:

	A	B	C	D
Start Date	End Date	#Days
Not Started	Not Started

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"][/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]74[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]2/10/2018[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]3/14/2018[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]68[/TD]

</tbody>

Sheet22​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(A2="","Not Started",IF(B2="",TODAY()-A2,B2-A2))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(A2="","Not Started",IF(B2="",DAYS(TODAY(),A2),DAYS(B2,A2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Is it possible to have an End Date, but no Start Date?

I'm sorry I went with the first formula. Thank you for sharing I learned something new! The first formula worked again

Thank you,

Bev

 

[jtakw]

You're very welcome, glad to help.

 

[bharbolt]

Can this same formula be applied to include working days instead of calendar days? I'm having a hard time getting them to work together.