Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of cells with date <today's date | New Users to Excel | |||
Count if date is between two dates and value in another column equ | Excel Worksheet Functions | |||
calculate the number of days from date received | Excel Worksheet Functions | |||
count number of years 2003 in a range of dates | Excel Worksheet Functions | |||
How do I count cells in a column of dates between date ranges? | Excel Worksheet Functions |