Day Counting - OPEN & CLOSED

D

Dax Arroway

I have three columns:
A=Start Date
B=End Date
C=Days

I'd like to count days if there is or is not an end date. So if the start
date was two weeks ago but there is no end date, I'd like the Days.column to
continue to increase as the days go by but end on the date entered into the
End.date column.

Seems easy but it's the easy things that elude me sometimes. Can someone
please help me with a formula for that?

Thanks in advance!!!
Dax
 
J

John C

C2: =IF(B2="",TODAY()-A2,B2-A2)
or, so long as no end dates are in the future, you could eliminate the IF
statement:
C2: =MAX(MIN(B2,TODAY()))-A2
 
D

Dax Arroway

Thank you,. that's perfect! I'd like to insert it throughout the worksheet
but weird numbers are appearing for the rows without start dates. Is there a
way of telling it to ingnore them? Also a correction, my colums are
A: Startdate
F: Enddate
G: DayCounter
 
J

John C

Well, weird numbers isn't very descriptive, more info needed.
G2: =IF(F2="",TODAY()-A2,F2-A2)
or
G2: =MAX(MIN(F2,TODAY()))-A2

If these start looking like dates, perhaps you need to format the cells as
numbers (with no decimals).
 
D

Dax Arroway

Something like:
G: =IF(A20="",ignore,F20="",TODAY()-A20,F20-A20)

But that's too many arguments, right? (and not the right syntax I'm guessing)
 
D

Dax Arroway

Startdate and Endate columns are formated throughout the worksheet as dates.
Days counted (Cloumn G) is formatted as General. The formula works perfect
but when there are no dates in either columns it gives me a long number in
the days counted column (39755 to be exact). I'd the cell left blank until
there's a date placed into the startdate column. (There will never be an
instance of no end date.)
Is that more clear?
 
J

John C

Okay. I assumed a start date was always present. You could use either of the
following:
G2: =IF(A2="","",IF(F2="",TODAY()-A2,F2-A2))
or
G2: =(MAX(MIN(F2,TODAY()))-A2)*(A2<>"") ... this will return a 0 if no
start date is entered, so, if you want it blank, use the first formula.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top