Keep a count number of days open

**Bob2003** · 08-31-2012, 11:03 AM

I want to keep a count for each ticket on how many days it remaind open or is still open. For example, when a date is entered into A7 (Open Date) then in L7 (Total day Open) a count of how many days from the current day it has been open (today()), once a date is entered in K7 (Close date) the count will stop.
Work Request Trouble Tickets.xlsx

**daddylonglegs** · 08-31-2012, 11:16 AM

Try this formula in L7

=IF(A7="","",IF(K7="",TODAY(),K7)-A7)

**Bob2003** · 08-31-2012, 11:22 AM

Originally Posted by daddylonglegs

Try this formula in L7

=IF(A7="","",IF(K7="",TODAY(),K7)-A7)

That worked! Can you explain it to me, im trying to learn/understand these formulas. Thanks

**Kevin UK** · 08-31-2012, 11:24 AM

Or maybe!

=IF(A7="","",IF(K7="","",(K7-A7)+1))
That's assuming that you are counting the full days

Ignore my post, did'nt read it right!

**daddylonglegs** · 08-31-2012, 11:29 AM

Originally Posted by Bob2003

....Can you explain it to me,.....

OK the first IF checks if A7 is blank....and if it is it returns a blank

=IF(A7="","",......)

If it isn't blank you can just subtract the date in A7 from today's date to get the number of days elapsed up to today so that then becomes

=IF(A7="","",TODAY()-A7)

......but if there's a date in K7 you want to use that K7 date instead of today, so in place of TODAY() in the formula you can use another IF function that will return today's date if K7 is blank, K7 otherwise, so that makes it:

=IF(A7="","",IF(K7="",TODAY(),K7)-A7)

If A7 is yesterday and K7 is blank that will return a value of 1 (1 day from yesterday to today). If you want to count both yesterday and today (and get 2) then add 1 as Kevin suggests, i.e.

=IF(A7="","",IF(K7="",TODAY(),K7)-A7+1)

**Bob2003** · 08-31-2012, 11:39 AM

Thanks for the info.. is there a way to click on a cell and it auto sort all cell that meet a certian criteria? For example if i clicked on closed it would bring to the top all the closed tickets, or click less than 21 day and bring those tickets to the top.