[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?

[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
---
"Mrs H" wrote:
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?

[Tyro[_3_]]

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

"Mrs H" <Mrs wrote in message
...
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?

[Michael.Tarnowski]

On Feb 6, 1:12 am, Max wrote:
=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
Singaporehttp://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

"Mrs H" wrote:
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?

Try something like

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

and format the cells as dates
Cheers Michael

[Michael.Tarnowski]

On Feb 6, 3:36 pm, "Michael.Tarnowski" wrote:
On Feb 6, 1:12 am, Max wrote:



=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
Singaporehttp://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

"Mrs H" wrote:
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?

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