|
Count days in between or if not entered till TODAY
Hello:
I'm trying to count days between two columns, it works fine till the "Current" column doesn't have an entry bcs an item hasn't been completed. In that case I want to count the days till "TODAY" since its still counting...any help? Do I need to use an IF with "Error" to accomplish this? i.e. Original Date Current/Completion Date 06/08/09 08/17/09 09/03/09 - 04/23/08 02/08/09 Simple formula works if both columns are populated but in a case like the row two above when the current day is not populated yet I'm getting error. Please help. Thank you. Monika |
Count days in between or if not entered till TODAY
=if(B1 = "", Today(), B1) - A1
-- HTH... Jim Thomlinson "murkaboris" wrote: Hello: I'm trying to count days between two columns, it works fine till the "Current" column doesn't have an entry bcs an item hasn't been completed. In that case I want to count the days till "TODAY" since its still counting...any help? Do I need to use an IF with "Error" to accomplish this? i.e. Original Date Current/Completion Date 06/08/09 08/17/09 09/03/09 - 04/23/08 02/08/09 Simple formula works if both columns are populated but in a case like the row two above when the current day is not populated yet I'm getting error. Please help. Thank you. Monika |
Count days in between or if not entered till TODAY
Hello Jim:
It didn't work, still getting #VALUE! error when in the column "B" I have the "-" value for a no date. Please advise. Thx Monika "Jim Thomlinson" wrote: =if(B1 = "", Today(), B1) - A1 -- HTH... Jim Thomlinson "murkaboris" wrote: Hello: I'm trying to count days between two columns, it works fine till the "Current" column doesn't have an entry bcs an item hasn't been completed. In that case I want to count the days till "TODAY" since its still counting...any help? Do I need to use an IF with "Error" to accomplish this? i.e. Original Date Current/Completion Date 06/08/09 08/17/09 09/03/09 - 04/23/08 02/08/09 Simple formula works if both columns are populated but in a case like the row two above when the current day is not populated yet I'm getting error. Please help. Thank you. Monika |
Count days in between or if not entered till TODAY
Try this:
=IF(B1="",TODAY(),B1)-A1 Format as General or Number -- Biff Microsoft Excel MVP "murkaboris" wrote in message ... Hello: I'm trying to count days between two columns, it works fine till the "Current" column doesn't have an entry bcs an item hasn't been completed. In that case I want to count the days till "TODAY" since its still counting...any help? Do I need to use an IF with "Error" to accomplish this? i.e. Original Date Current/Completion Date 06/08/09 08/17/09 09/03/09 - 04/23/08 02/08/09 Simple formula works if both columns are populated but in a case like the row two above when the current day is not populated yet I'm getting error. Please help. Thank you. Monika |
Count days in between or if not entered till TODAY
Jim:
sorry -- it actually works if I remove the "-" symbol for the cells that don't have a date yet. Is there a way to make the formula works while keeping the symbol in the cell -- didn't want to have blank cells but if this is the only way I'll keep it that way. Thanks for your help. Monika "Jim Thomlinson" wrote: =if(B1 = "", Today(), B1) - A1 -- HTH... Jim Thomlinson "murkaboris" wrote: Hello: I'm trying to count days between two columns, it works fine till the "Current" column doesn't have an entry bcs an item hasn't been completed. In that case I want to count the days till "TODAY" since its still counting...any help? Do I need to use an IF with "Error" to accomplish this? i.e. Original Date Current/Completion Date 06/08/09 08/17/09 09/03/09 - 04/23/08 02/08/09 Simple formula works if both columns are populated but in a case like the row two above when the current day is not populated yet I'm getting error. Please help. Thank you. Monika |
Count days in between or if not entered till TODAY
Try it like this:
=IF(B1="-",TODAY(),B1)-A1 Or: =IF(COUNT(B1),B1,TODAY())-A1 -- Biff Microsoft Excel MVP "murkaboris" wrote in message ... Jim: sorry -- it actually works if I remove the "-" symbol for the cells that don't have a date yet. Is there a way to make the formula works while keeping the symbol in the cell -- didn't want to have blank cells but if this is the only way I'll keep it that way. Thanks for your help. Monika "Jim Thomlinson" wrote: =if(B1 = "", Today(), B1) - A1 -- HTH... Jim Thomlinson "murkaboris" wrote: Hello: I'm trying to count days between two columns, it works fine till the "Current" column doesn't have an entry bcs an item hasn't been completed. In that case I want to count the days till "TODAY" since its still counting...any help? Do I need to use an IF with "Error" to accomplish this? i.e. Original Date Current/Completion Date 06/08/09 08/17/09 09/03/09 - 04/23/08 02/08/09 Simple formula works if both columns are populated but in a case like the row two above when the current day is not populated yet I'm getting error. Please help. Thank you. Monika |
Count days in between or if not entered till TODAY
Hi
We use IF to check if Current/Complete is empty, then decide whitch formula to use: =IF(B12<"",B12-A12,TODAY()-A12) Regards, Per "murkaboris" skrev i meddelelsen ... Hello: I'm trying to count days between two columns, it works fine till the "Current" column doesn't have an entry bcs an item hasn't been completed. In that case I want to count the days till "TODAY" since its still counting...any help? Do I need to use an IF with "Error" to accomplish this? i.e. Original Date Current/Completion Date 06/08/09 08/17/09 09/03/09 - 04/23/08 02/08/09 Simple formula works if both columns are populated but in a case like the row two above when the current day is not populated yet I'm getting error. Please help. Thank you. Monika |
Count days in between or if not entered till TODAY
Thank you Per
Monika "Per Jessen" wrote: Hi We use IF to check if Current/Complete is empty, then decide whitch formula to use: =IF(B12<"",B12-A12,TODAY()-A12) Regards, Per "murkaboris" skrev i meddelelsen ... Hello: I'm trying to count days between two columns, it works fine till the "Current" column doesn't have an entry bcs an item hasn't been completed. In that case I want to count the days till "TODAY" since its still counting...any help? Do I need to use an IF with "Error" to accomplish this? i.e. Original Date Current/Completion Date 06/08/09 08/17/09 09/03/09 - 04/23/08 02/08/09 Simple formula works if both columns are populated but in a case like the row two above when the current day is not populated yet I'm getting error. Please help. Thank you. Monika |
Count days in between or if not entered till TODAY
Thank you.
"T. Valko" wrote: Try it like this: =IF(B1="-",TODAY(),B1)-A1 Or: =IF(COUNT(B1),B1,TODAY())-A1 -- Biff Microsoft Excel MVP "murkaboris" wrote in message ... Jim: sorry -- it actually works if I remove the "-" symbol for the cells that don't have a date yet. Is there a way to make the formula works while keeping the symbol in the cell -- didn't want to have blank cells but if this is the only way I'll keep it that way. Thanks for your help. Monika "Jim Thomlinson" wrote: =if(B1 = "", Today(), B1) - A1 -- HTH... Jim Thomlinson "murkaboris" wrote: Hello: I'm trying to count days between two columns, it works fine till the "Current" column doesn't have an entry bcs an item hasn't been completed. In that case I want to count the days till "TODAY" since its still counting...any help? Do I need to use an IF with "Error" to accomplish this? i.e. Original Date Current/Completion Date 06/08/09 08/17/09 09/03/09 - 04/23/08 02/08/09 Simple formula works if both columns are populated but in a case like the row two above when the current day is not populated yet I'm getting error. Please help. Thank you. Monika |
Count days in between or if not entered till TODAY
You're welcome!
-- Biff Microsoft Excel MVP "murkaboris" wrote in message ... Thank you. "T. Valko" wrote: Try it like this: =IF(B1="-",TODAY(),B1)-A1 Or: =IF(COUNT(B1),B1,TODAY())-A1 -- Biff Microsoft Excel MVP "murkaboris" wrote in message ... Jim: sorry -- it actually works if I remove the "-" symbol for the cells that don't have a date yet. Is there a way to make the formula works while keeping the symbol in the cell -- didn't want to have blank cells but if this is the only way I'll keep it that way. Thanks for your help. Monika "Jim Thomlinson" wrote: =if(B1 = "", Today(), B1) - A1 -- HTH... Jim Thomlinson "murkaboris" wrote: Hello: I'm trying to count days between two columns, it works fine till the "Current" column doesn't have an entry bcs an item hasn't been completed. In that case I want to count the days till "TODAY" since its still counting...any help? Do I need to use an IF with "Error" to accomplish this? i.e. Original Date Current/Completion Date 06/08/09 08/17/09 09/03/09 - 04/23/08 02/08/09 Simple formula works if both columns are populated but in a case like the row two above when the current day is not populated yet I'm getting error. Please help. Thank you. Monika |
Hey all,
I need a bit more help although the previous posts have been excellent. I am using similar although not exactly the same functionality to track how long it takes to get service tickets that are billable turned in from my field personnel. The formula works great with the exception that if there is no start date then the formula always returns a value of 41269. Any idea's on how I can prevent any value from showing (or have a 0 display) if there is no start date? Thanks tons!!! Quote:
|
1 Attachment(s)
Quote:
S. |
| All times are GMT +1. The time now is 11:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com