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!

 

[Purple_Girl]

Hmm...I am not sure how the dates are being generated, as the spreadsheet belongs to someone else, who reached out to me for assistance with that formula. So, that's why the "--" needed to be added before the J cell location?

I checked the format in column J, and it shows that it is in the Date format with 2-digit month, 2-digit day, and 2-digit year - however, it does not reflect that format within the cells in column J. They appear in the "1/1/2011" format...strange.

 

[Purple_Girl]

Ok, even though the column J values were in the "date" format, I went into a few cells and retyped the values - and the format "took"...after that, I tried the original formula (without the "--") and it worked just fine. You are right, however the values in column J are being generated, that is the problem.

 

[jasonb75]

It is possible that the format was changed after the dates were entered.

If you enter anything into a cell that is formatted as text, then change the format of the cell, it will stay as text until you re-enter the value.

This can be done, either by re-typing, double click then enter, or if you have a lot of cells, select them all, then in the menu / ribbon navigate to Data > Text to columns and click Finish.