| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to count number of time stamps within a range in a column having dates formatted as "custom" | Excel Discussion (Misc queries) | |||
| Provide list from all columns marked with and "x" | Excel Worksheet Functions | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| create links to check boxes marked "good" fair"and "bad" | Excel Worksheet Functions | |||
| How do I count like dates in a column with format "January-05"? | Excel Worksheet Functions | |||
Linear Mode
