calculating date differences

[damon9868]

Hi Guys

I'm a newbie here, and a very basic excel user, so please excuse if this is a daft question...

I'm trying to devise a method of reporting delays on a construction project due to lack of timely information.

In simple terms, a specific query is raised, and a drop dead date for an answer is required. More often than not the date for that answer is exceeded, creating a delay.

I can create a simple formula subtracting one date from another, eg =A2-A1, where A1 is the date required and A2 is the date received. - however, that only works when both dates are known.

For example, if I request an answer by 13th March, and receive the answer on 20th March, that creates a 7 day delay; conversely if I receive an answer on 11th March it should generate a 2 day saving (in practice no savings are actually generated, so in that case I would want the result to be zero)
However, I cannot enter a date value until I actually receive a response, and in that case I believe excel defaults to 1st Jan 1900.
On that basis my required date of 13th March against an answered date of 1st Jan 1900 generates a value of -43903 days...

How do I get around this?

Any assistance would be greatly appreciated.

[dosydos]

Perhaps utilizing some if statements and the TODAY() function.
Try this formula below.

=IF(IF(A2="",TODAY()-A1,A2-A1)<0,0,IF(A2="",TODAY()-A1,A2-A1))

This states if there is no data in A2 then take todays date and subtract by date required. The first IF statement says that if A2-A1 is less than 0 or a (savings) as you put it, then to return 0. So usuing your 3/13/20 example, this will return 0 until a date is put into A2 or todays date is 3/14/20 or later

[damon9868]

Dosydos, Thank you for advice - you've cooked my brain with that formula, but it worked a treat, thank you.

One more question, sorry.

As I'm creating a template (which will have formulae, but no values) for others to complete, are you able to show me a formula to avoid the default 1st Jan 1900 if neither date are entered.

If I use your formula when neither A1 nor A2 have entered values, then the default 1st Jan 1900 comes into play again...

Any advice greatly appreciated

[dosydos]

=IF(A1="","",IF(IF(A2="",TODAY()-A1,A2-A1)<0,0,IF(A2="",TODAY()-A1,A2-A1)))

this will make it if A1 has no value then the answer will just be blank.
**I am under the assumption that if A1 is blank A2 will ALWAYS be blank

[damon9868]

Dosydos, I am indebted to you, and I wish I had your logical mind. Thank you, this has worked a treat.