If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count number of days between dates BUT IF null to current date
I have a question I'm hoping someone can help me with. I need to know how
many days an invoice was open. Column A2:A500 is the open date and Column B2:B500 is closed out date. I need an "IF B is null" added onto my original =+IF(K4=J4,1,DAYS360(J4,K4)+1) so that if there is a closed date then count the number of days between the open date and close date BUT IF there isn't a close date just count the number of days from the open date until the current date. Can anyone help? |
#2
|
|||
|
|||
Count number of days between dates BUT IF null to current date
=MAX(IF(ISBLANK(B2),TODAY(),B2)-A2,1)
Make sure A and B are dates. Be sure to format the cell containing the formula with something like this, otherwise it will look like a funny date... # " days" You would normally just use B2-A2 to get the difference in dates, but in your case you need a little bit more. The IF decides wether to use A2 or Today() depending on if A2 is blank. The MAX portion outputs 1 if the close date is less than A2 (based on your formula). There really is no need to ever use the DAYS360 if you understand how excel works with dates. Look under "How Microsoft Excel stores dates and times" in the help. "kathi" wrote: I have a question I'm hoping someone can help me with. I need to know how many days an invoice was open. Column A2:A500 is the open date and Column B2:B500 is closed out date. I need an "IF B is null" added onto my original =+IF(K4=J4,1,DAYS360(J4,K4)+1) so that if there is a closed date then count the number of days between the open date and close date BUT IF there isn't a close date just count the number of days from the open date until the current date. Can anyone help? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I count the number of days from 1 date to another? | Steve R | General Discussion | 3 | February 2nd, 2006 08:58 PM |
Report that generates number of working days within two date range | Sam | Setting Up & Running Reports | 0 | September 23rd, 2005 03:33 PM |
Query for 'confirmation' | rogge | Running & Setting Up Queries | 8 | April 19th, 2005 03:26 PM |
How to calculate days:Hours:Minutes: | Kurewe | Worksheet Functions | 46 | January 17th, 2005 11:11 AM |
CALCULATE FUTURE DATES | Michael J. Malinsky | Worksheet Functions | 5 | February 5th, 2004 04:42 PM |