Formula help with calculating date range with a condition!!!

[Purple_Girl]

Hello,

I am trying to calculate the following:

If the Date in column J is less than or equal to the Date in column S, then calculate the number of days between column T and column S

Otherwise, if the Date in column J is greater than the Date in column S, then calculate the number of days between column J and column T

J (start date)
S (another start date)
T (end date)

Can anyone out there help me?!!! I have been spinning my wheels for a looong time on this. My current formula works if the date in column J is on the "1st" of the month (i.e., 1/1/2011), but if it is on another day of the month (i.e., 1/7/2011), it doesn't give me the correct total - it subtracts or adds the number of days from the 1st of the month...very strange. Therefore, my formula works for some (that start on the "1st"), but not all!!

Formulas I've tried:

=IFERROR(IF(DATE(YEAR(J3)+5,MONTH(J3),DAY(J3)<S3),DATEDIF(S3,T3,"MD"),DATEDIF(J3,T3,"MD"))," ")


=IFERROR(IF(J3<S3,DATEDIF(S3,T3,"MD"),DATEDIF(J3,T3,"MD")),"-")

I appreciate any help I can get!!!

Thank you!

 

[BobUmlas]

=if(j3<=s3,t3-s3,f3-t3)+1

 

[Purple_Girl]

Thanks for responding! I've tried that one already (without the "f" - I think that was a typo), also and it is not working...that's why I ended up using the "datedif", because the condition for the date seems to be throwing the formula off...it won't calculate that way.

Any other ideas on a formula I could use?

 

[jasonb75]

Try

=T3-MAX(J3,S3)

 

[Purple_Girl]

Jason, thanks for your response...I just tried, and it is not working either. It gives me a value of "20"...here's what I have:

Column J
3/3/2011

Column S
3/1/2011

Column T
3/21/2011

It seems that what keeps happening, is that when Column J is 3/1/2011, the total comes out fine - "20". But, whenever the date in Column J is greater than 3/1/2011, it does not give the correct value. The formula should have given me "18" instead of "20", right?

 

[jasonb75]

That's the result I get

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 76px"><COL style="WIDTH: 76px"><COL style="WIDTH: 76px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>D</TD><TD>J</TD><TD>S</TD><TD>T</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">18</TD><TD style="TEXT-ALIGN: right">03/03/2011</TD><TD style="TEXT-ALIGN: right">03/01/2011</TD><TD style="TEXT-ALIGN: right">03/21/2011</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D3</TD><TD>=T3-MAX(J3,S3)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

 

[Purple_Girl]

Yours totally looks right, but I keep getting a value of 20...which is driving me up a wall. I don't understand why I keep getting the wrong value...? Can you view this?

http://tinypic.com/r/33k4xs8/7

 

[jasonb75]

See if
=T3-MAX(--J3,S3)

works.

 

[Purple_Girl]

Oh, that worked, THANKS SO MUCH!!!!! You were a GREAT help!!!! Many thanks to you!!!

 

[jasonb75]

How are the dates in the 3 cells being generated?

The reason the original formula(s) failed is because the date in column J is a text string, not a valid numeric date.