Count between dates with some entries marked as "-"
 

I need to calculated number of days between two dates where some of the
entries didn't occur yet thus have a "-" sign in the cell. The cells are
formatted in date format of MM/DD/YY.

I've tried following formulas that work if both dates are populated but give
me a #VALUE! error if one of the cells has the "-" sign it.

Does anybody know of any other way to include it so if the sign is there the
formula calculates the difference all the way to today?

=IF(B1="",TODAY(),B1)-A1
=IF(B1<"",B1-A1,TODAY()-A1)
=IF(B1="-",TODAY(),B1)-A1

all of the return the same error and work only of both dates are populated.
Please help.

Thank you
Monika

Count between dates with some entries marked as "-"
 

If you click on the cell that has a - in it, what does the formula bar show?
Possibly 0? If so:

=IF(B1=0,TODAY(),B1)-A1

otherwise, change 0 to the value in the formula bar.

"murkaboris" wrote:

I need to calculated number of days between two dates where some of the
entries didn't occur yet thus have a "-" sign in the cell. The cells are
formatted in date format of MM/DD/YY.

I've tried following formulas that work if both dates are populated but give
me a #VALUE! error if one of the cells has the "-" sign it.

Does anybody know of any other way to include it so if the sign is there the
formula calculates the difference all the way to today?

=IF(B1="",TODAY(),B1)-A1
=IF(B1<"",B1-A1,TODAY()-A1)
=IF(B1="-",TODAY(),B1)-A1

all of the return the same error and work only of both dates are populated.
Please help.

Thank you
Monika

Count between dates with some entries marked as "-"
 

Hi Sean:

Thanks for the response.
The formula bar shows "-" so that's what I put in my formula but it still
returns the #VALUE! error.

Monika

"Sean Timmons" wrote:

If you click on the cell that has a - in it, what does the formula bar show?
Possibly 0? If so:

=IF(B1=0,TODAY(),B1)-A1

otherwise, change 0 to the value in the formula bar.

"murkaboris" wrote:

I need to calculated number of days between two dates where some of the
entries didn't occur yet thus have a "-" sign in the cell. The cells are
formatted in date format of MM/DD/YY.

I've tried following formulas that work if both dates are populated but give
me a #VALUE! error if one of the cells has the "-" sign it.

Does anybody know of any other way to include it so if the sign is there the
formula calculates the difference all the way to today?

=IF(B1="",TODAY(),B1)-A1
=IF(B1<"",B1-A1,TODAY()-A1)
=IF(B1="-",TODAY(),B1)-A1

all of the return the same error and work only of both dates are populated.
Please help.

Thank you
Monika

Count between dates with some entries marked as "-"
 

Hi Sean:

Got it, I use IF in conjunction with ISERROR and got it working.
Thx
Monika

"Sean Timmons" wrote:

If you click on the cell that has a - in it, what does the formula bar show?
Possibly 0? If so:

=IF(B1=0,TODAY(),B1)-A1

otherwise, change 0 to the value in the formula bar.

"murkaboris" wrote:

I need to calculated number of days between two dates where some of the
entries didn't occur yet thus have a "-" sign in the cell. The cells are
formatted in date format of MM/DD/YY.

I've tried following formulas that work if both dates are populated but give
me a #VALUE! error if one of the cells has the "-" sign it.

Does anybody know of any other way to include it so if the sign is there the
formula calculates the difference all the way to today?

=IF(B1="",TODAY(),B1)-A1
=IF(B1<"",B1-A1,TODAY()-A1)
=IF(B1="-",TODAY(),B1)-A1

all of the return the same error and work only of both dates are populated.
Please help.

Thank you
Monika

Count between dates with some entries marked as "-"
 

Could you ensure there are no leading or trailing spaces with that - ? can
use a find() instead..

=IF(iserror(find("-",B1)),B1,TODAY())-A1


"murkaboris" wrote:

Hi Sean:

Thanks for the response.
The formula bar shows "-" so that's what I put in my formula but it still
returns the #VALUE! error.

Monika

"Sean Timmons" wrote:

If you click on the cell that has a - in it, what does the formula bar show?
Possibly 0? If so:

=IF(B1=0,TODAY(),B1)-A1

otherwise, change 0 to the value in the formula bar.

"murkaboris" wrote:

I need to calculated number of days between two dates where some of the
entries didn't occur yet thus have a "-" sign in the cell. The cells are
formatted in date format of MM/DD/YY.

I've tried following formulas that work if both dates are populated but give
me a #VALUE! error if one of the cells has the "-" sign it.

Does anybody know of any other way to include it so if the sign is there the
formula calculates the difference all the way to today?

=IF(B1="",TODAY(),B1)-A1
=IF(B1<"",B1-A1,TODAY()-A1)
=IF(B1="-",TODAY(),B1)-A1

all of the return the same error and work only of both dates are populated.
Please help.

Thank you
Monika

Count between dates with some entries marked as "-"
 

Ahh, beat me to it! :-)

"murkaboris" wrote:

I need to calculated number of days between two dates where some of the
entries didn't occur yet thus have a "-" sign in the cell. The cells are
formatted in date format of MM/DD/YY.

I've tried following formulas that work if both dates are populated but give
me a #VALUE! error if one of the cells has the "-" sign it.

Does anybody know of any other way to include it so if the sign is there the
formula calculates the difference all the way to today?

=IF(B1="",TODAY(),B1)-A1
=IF(B1<"",B1-A1,TODAY()-A1)
=IF(B1="-",TODAY(),B1)-A1

all of the return the same error and work only of both dates are populated.
Please help.

Thank you
Monika