Count number of days between dates BUT IF null to current date

K

kathi

I have a question I'm hoping someone can help me with. I need to know how
many days an invoice was open.
Column A2:A500 is the open date and
Column B2:B500 is closed out date. I need an
"IF B is null" added onto my original =+IF(K4<=J4,1,DAYS360(J4,K4)+1) so
that if there is a closed date then count the number of days between the open
date and close date BUT IF there isn't a close date just count the number of
days from the open date until the current date. Can anyone help?
 
S

Sloth

=MAX(IF(ISBLANK(B2),TODAY(),B2)-A2,1)

Make sure A and B are dates. Be sure to format the cell containing the
formula with something like this, otherwise it will look like a funny date...
# " days"

You would normally just use B2-A2 to get the difference in dates, but in
your case you need a little bit more.
The IF decides wether to use A2 or Today() depending on if A2 is blank.
The MAX portion outputs 1 if the close date is less than A2 (based on your
formula).

There really is no need to ever use the DAYS360 if you understand how excel
works with dates. Look under "How Microsoft Excel stores dates and times" in
the help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top