Count No of days past due

M

Mrs H

I need a formula to count the number of days from a date in a cell to the
current date if another cell is null.

Example:

Date Sent Date Received Days Past Due
1/29/09 Null or blank

Also I have this formula:
=TODAY()-K15 but if the cell it refers to is null, it returns a number
39849 instead of zero - why is this?
 
M

Max

=TODAY()-K15 but if the cell it refers to is null, it returns a number
39849 instead of zero - why is this?

If K15 is really blank, since Excel evaluates blanks as zeros in calcs,
you'd get a number which is: =TODAY()-0, ie: =TODAY(). If you format the
formula cell as date, it'll display it clearer.

If K15 contains a formula returned blank/null string: "" (ie text), or
contains any kind of text, then Excel will be unable to compute the
expression and it'll just return an error: #VALUE!
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---
 
T

Tyro

Excel dates are numbers. You have to format the number as a date to see a
date. A basic book on Excel will explain this.

Tyro
 
M

Michael.Tarnowski

If K15 is really blank, since Excel evaluates blanks as zeros in calcs,
you'd get a number which is: =TODAY()-0, ie: =TODAY(). If you format the
formula cell as date, it'll display it clearer.

If K15 contains a formula returned blank/null string: "" (ie text), or
contains any kind of text, then Excel will be unable to compute the
expression and it'll just return an error: #VALUE!
--
Max
Singaporehttp://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik

Try something like

DaysPastDue =IF(DateReceived=0;TODAY()-DateSent;0)

and format the cells as dates
Cheers Michael
 
M

Michael.Tarnowski

Try something like

DaysPastDue =IF(DateReceived=0;TODAY()-DateSent;0)

and format the cells as dates
Cheers Michael

you can use to format a custom format like: "DD. MMM YY ;;"
The ";;" prevents the display of zero values
Michael
 

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