Calculating elapsed time using two conditions

[Jazz Jones]

Hi,

I'm looking to calculate "Weeks Active" for vacancies that are advertising. I have "Date Opened" in Column P, "Date Closed" in Column Q and today's date in Column R.

If the closing date is populated, I want Column S to calculate time elapsed between Col P and Col Q. If the vacancy is still open, I want it to calculate between Col P and Col R.

I've tried to use the ISBLANK rule but I'm getting myself in a muddle with the whole concept. Would someone be able to steer me in the right direction, please? It's killing me!

Many thanks

Jasmine

 

[Rorylfc]

=IF(Q2="",R2-P2,Q2-P2)

This will give you the value in Days. Enter the code in S2 and copy down

 

[wideboydixon]

This?

Book1
	P	Q	R	S
1	Date Opened	Date Closed	Today	Weeks Active
2	01/03/2017		13/03/2017	1
3	01/01/2017		13/03/2017	10
4	01/01/2017	01/02/2017	13/03/2017	4
Sheet1

Cell Formulas
Range	Formula
R2	=TODAY()
S2	=IF($Q2="",INT(($R2-$P2)/7),INT(($Q2-$P2)/7))

WBD

 

[Jazz Jones]

These are both better than the way I finally managed it:
=IF(ISBLANK([@[Closing Date]]),([@Today]-[@[Date added]]),([@[Closing Date]]-[@[Date added]]))

Thank you - I will update so that it looks less messy! VERY much appreciated, guys.