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
1	Start Date	End Date	#Days
2	1/1/2018		74	74
3	1/1/2018	2/10/2018	40	40

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet22​

Worksheet Formulas Cell Formula C2 =IF(B2="",TODAY()-A2,B2-A2) D2 =IF(B2="",DAYS(TODAY(),A2),DAYS(B2,A2)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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
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

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet22​

Worksheet Formulas Cell 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))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.