Microsoft Excel - Aging formulas - Asked By Daniel Rakers on 29-Jan-11 02:09 PM

I found this in an earlier post could someone help with my question?

This is a formula for tracking aging by 2 dates in an Excel spread sheet. This will show a blank field should both date fields have no data, is there an addition to this formula to make the field blank also if say the (A) field shows a date but the (V) field has no data yet? The field when the (A) field has a date shows 40548.

Thank you in advance for any assistance with this question.

=IF(AND(A8="",V8=""),"",IF(A8="",TODAY()-A8,V8-A8))


Dan
John D replied to Daniel Rakers on 29-Jan-11 05:28 PM
Hi Daniel
Try this: =IF(AND(OR(A8="",V8="")),"",IF(A8="",TODAY()-A8,V8-A8))
HTH
John
Daniel Rakers replied to John D on 29-Jan-11 05:34 PM
Thanks that worked great! I just found another small issue? Can you help? I also want to add the networkdays statement to only track work days.

Sorry I'm abit Excel formula challenged.

Thanks.

Dan
John D replied to Daniel Rakers on 29-Jan-11 06:12 PM
Hi Daniel
Your not giving any cell reference.
So...
Networkdays
This function will calculate the number of working days between two dates.
It will exclude weekends and any holidays.

Syntax
 =NETWORKDAYS(StartDate,EndDate,Holidays)
   Holidays : This is a list of dates which will be excluded from the calculation, such as Xmas.
Example:
   A1            B1           C1
3/1/2011-----4/6/2011-----answer 27
 =NETWORKDAYS(B28,C28,C30:C42)
 C30:C42 is a list of holiday dates you would type in.
These functions show #NAME if the Analysys Toolpak is not installed.
HTH
John
Daniel Rakers replied to John D on 29-Jan-11 06:22 PM
Hey John,

It was the same reference cells in the original formula you edited for me: =IF(AND(OR(A8="",V8="")),"",IF(A8="",TODAY()-A8,V8-A8))
 
This is what I have so far for my other formula but I was having the same issue were the field shows a number (18) not blank when the end date has not been input yet? It does only track work days no holidays. See below:

=IF(A6="","",NETWORKDAYS(A6,IF(V6="",TODAY(),V6)))

As I said I'm not very good yet with Excel just a beginner. I also do not want to necessarily include holidays if there may be 2 versions you can help with

I appreciate all your help.

Dan
John D replied to Daniel Rakers on 29-Jan-11 06:44 PM
Is it this that you want, you don't need to use the reference for holidays, just ignore it.
=IF(AND(OR(A6="",V6="")),"",NETWORKDAYS(A6,IF(V6="",TODAY(),V6)))
John
Daniel Rakers replied to John D on 29-Jan-11 06:48 PM
Bingo!

You rock!

Thanks for all the help I really appreciate it. I'm learning slowly but it's a bit confusing. Can you suggest any literature that may help with formulas or is it just trial and error?

Have a great weekend.

Dan
John D replied to Daniel Rakers on 29-Jan-11 07:17 PM
Hi again
Just Google "Books on Excel formula" you can go on Youtube and do a search for excel formula and it's full of video to watch.
If you read these excel forums and try to solve some of the problems are just check the answers others post that would help.
Don't expect to be an expert overnight, I'm not.
The more you learn, the more you realize you know nothing..(-:
Keep on typing
John
Jackpot . replied to John D on 29-Jan-11 10:42 PM
Hi John

Have you tried the formula you posted.

What does the AND() do in your formula.
Try the formula keeping V6 blank..The OR() statement will not allow the formula to exeucte NETWORKDAYS() if V6 = ""; which is not what the OP is looking for..


=IF(A6="","",NETWORKDAYS(A6,IF(V6="",TODAY(),V6)))
John D replied to Jackpot . on 31-Jan-11 08:49 AM
Hi Jackpot
Yes your right, the "and" is redundant,
Thanks
John