DateDIF
 

Hello --

I have to keep track of a number of certificates and licenses.
I have a sheet where I have in a column when the license was obtained and
another column when the license expires. There is a third column that
calculates the difference in days between the two.
When the certificate expires I get an out of range error.
Can I capture that error and make the cell simply say: "Expired" ?

Thanks,
Paul

DateDIF
 

Hi Paul

You don't provide much detail on how and where this is done, but try
something like
=IF(B1<A1,"expired",B1-A1)

HTH. Best wishes Harald

"Paul Breslin" skrev i melding
.. .
Hello --

I have to keep track of a number of certificates and licenses.
I have a sheet where I have in a column when the license was obtained and
another column when the license expires. There is a third column that
calculates the difference in days between the two.
When the certificate expires I get an out of range error.
Can I capture that error and make the cell simply say: "Expired" ?

Thanks,
Paul

DateDIF
 

OK, let me be more specific ...

Here are the cell values:
In cell C1 I list today's date with a "NOW()"

On the worksheet I use these values:
A10 = a consecutive number
B10 = the name of a license of certificate
C10 = the date of the last survey
D10 = the date of the next survey
E10 = the expiration date
F10 = Days until expiration

It is in cell F10 I have: =DATEDIF($C$1,$E$10,"D")

This works very well giving the number of days until expiration. The
function looks at the current date in C1 and looks at the date in E10 and
calculates the number of days until expiration.
However, if the certificate had expired; that is, if the value in E10 is
less than the value in C1, then the function returns "#NUM". I want to stop
that and have it return "EXPIRED" instead.

Thanks,
Paul
----------

"Harald Staff" wrote in message
...
Hi Paul

You don't provide much detail on how and where this is done, but try
something like
=IF(B1<A1,"expired",B1-A1)

HTH. Best wishes Harald

"Paul Breslin" skrev i melding
.. .
Hello --

I have to keep track of a number of certificates and licenses.
I have a sheet where I have in a column when the license was obtained and
another column when the license expires. There is a third column that
calculates the difference in days between the two.
When the certificate expires I get an out of range error.
Can I capture that error and make the cell simply say: "Expired" ?

Thanks,
Paul

DateDIF
 

I see. You did try to implement my suggestion ?
=IF(E10<$C$1,"expired",E10-$C$1)

HTH. Best wishes Harald

"Paul Breslin" skrev i melding
.. .
OK, let me be more specific ...

Here are the cell values:
In cell C1 I list today's date with a "NOW()"

On the worksheet I use these values:
A10 = a consecutive number
B10 = the name of a license of certificate
C10 = the date of the last survey
D10 = the date of the next survey
E10 = the expiration date
F10 = Days until expiration

It is in cell F10 I have: =DATEDIF($C$1,$E$10,"D")

This works very well giving the number of days until expiration. The
function looks at the current date in C1 and looks at the date in E10 and
calculates the number of days until expiration.
However, if the certificate had expired; that is, if the value in E10 is
less than the value in C1, then the function returns "#NUM". I want to
stop
that and have it return "EXPIRED" instead.

Thanks,
Paul
----------

"Harald Staff" wrote in message
...
Hi Paul

You don't provide much detail on how and where this is done, but try
something like
=IF(B1<A1,"expired",B1-A1)

HTH. Best wishes Harald

"Paul Breslin" skrev i melding
.. .
Hello --

I have to keep track of a number of certificates and licenses.
I have a sheet where I have in a column when the license was obtained
and
another column when the license expires. There is a third column that
calculates the difference in days between the two.
When the certificate expires I get an out of range error.
Can I capture that error and make the cell simply say: "Expired" ?

Thanks,
Paul

DateDIF
 

Yes, this works very well - thank you.
And, by manipulating the decimals, setting them to zero, it shows the number
of days until expiration as whole days.
Thanks again,
Paul


"Harald Staff" wrote in message
...
I see. You did try to implement my suggestion ?
=IF(E10<$C$1,"expired",E10-$C$1)

HTH. Best wishes Harald

"Paul Breslin" skrev i melding
.. .
OK, let me be more specific ...

Here are the cell values:
In cell C1 I list today's date with a "NOW()"

On the worksheet I use these values:
A10 = a consecutive number
B10 = the name of a license of certificate
C10 = the date of the last survey
D10 = the date of the next survey
E10 = the expiration date
F10 = Days until expiration

It is in cell F10 I have: =DATEDIF($C$1,$E$10,"D")

This works very well giving the number of days until expiration. The
function looks at the current date in C1 and looks at the date in E10 and
calculates the number of days until expiration.
However, if the certificate had expired; that is, if the value in E10 is
less than the value in C1, then the function returns "#NUM". I want to
stop
that and have it return "EXPIRED" instead.

Thanks,
Paul
----------

"Harald Staff" wrote in message
...
Hi Paul

You don't provide much detail on how and where this is done, but try
something like
=IF(B1<A1,"expired",B1-A1)

HTH. Best wishes Harald

"Paul Breslin" skrev i melding
.. .
Hello --

I have to keep track of a number of certificates and licenses.
I have a sheet where I have in a column when the license was obtained
and
another column when the license expires. There is a third column that
calculates the difference in days between the two.
When the certificate expires I get an out of range error.
Can I capture that error and make the cell simply say: "Expired" ?

Thanks,
Paul