Difference between dates using IF and TODAY...stuck!

[Related1]

Hi!

I have two columns. If there is a date in the first column, and no date in the second column I would like the second column to be today's date and then continue with my formula to determine the number of days between the two dates.

=networkdays(E14, F14,Holidays!$A$2:$A$15).

If there is no date in the first column, a blank can be returned.

Honestly, I was impressed with myself that I figured out the second part which works when there are dates but I always have a hard time connecting/nesting functions.

Thanks for any help!

 

[Sufiyan97]

Try

=IF(E14="","",IF(F14="",TODAY()-NETWORKDAYS(E14,F14,$A$2:$A$15),NETWORKDAYS(E14,F14,$A$2:$A$15)))

 

[Related1]

Hi! Thanks for the help. When you see it written out it makes so much more sense.

I used =IF(E52="","",IF(F52="",TODAY()-NETWORKDAYS(E52,F52,Holidays!$A$2:$A$15),NETWORKDAYS(E52,F52,Holidays!$A$2:$A$15))) based off of your suggestion and I received this?

But I copied it directly and added 'Holidays' which is the name of the sheet that contains A2:A15.

The cells in column G are formatted as follows:



My assumption is that I did something wrong!

 

[Sufiyan97]

Try

=IF(E52="","",NETWORKDAYS(E52,IF(F52="",TODAY(),F52),Holidays!$A$2:$A$15))

 

[Related1]

YES!!!! Do you mind explaining how this worked and why the other one didn't? I'd like to learn.

THANK YOU.

 

[Sufiyan97]

YES!!!! Do you mind explaining how this worked and why the other one didn't? I'd like to learn.

THANK YOU.

Sure

I made a mistake in previous formula
Below part of formula is totally wrong because in F52 is blank then it will deduct TODAY from start date

=IF(E52="","",IF(F52="",TODAY()-NETWORKDAYS(E52,F52,$A$2:$A$15),NETWORKDAYS(E52,F52,$A$2:$A$15)))

and because we already deduct F52 (end date) here =NETWORKDAYS(E52,F52,$A$2:$A$15)

It gives -31769 and if we deduct it from TODAY's date which is 44539 in (General Format)
It gives 76308

If you put below formula only and deduct it from today's date then you will get an idea that what happens

=NETWORKDAYS(E52,F52,$A$2:$A$15)

Hope this helps!

 

[Related1]

Yes, it totally helps and makes sense. I like following the logic on how you discovered the mistake!

Thanks again!

 

[Sufiyan97]

You're Welcome!
Glad I was able to help!

 

[Related1]

I have one more request

How would I also add an IF where if there are two dates there - I do NOT need the calculation? (It's calculated in another column).
Formula used =IF(E12="","",NETWORKDAYS(E12,IF(F12="",TODAY(),F12),Holidays!$A$2:$A$15))

So if there are two dates in E12 and F12, return " ".

Is this possible?

Thanks.

 

[Fluff]

How about

=IF(AND(E12<>"",F12<>""),"",IF(E12="","",NETWORKDAYS(E12,IF(F12="",TODAY(),F12),Holidays!$A$2:$A$15)))