calculating dates...

[coolhanddan]

Hi Everyone,

Is it possible to calculate dates based on information from more than one cell? In this case, I would like to calculate a due date 90 days after a date entered in cell E2, if the date is before the date in cell F2. Essentially, if someone is part of the data, their enrollment and discharge are in cell E and F respectively, and due dates follow. So if someone enrolled on June 2, 2011, was discharged on June 2, 2011, and there is something every 90 days, I would like to calculate that in collumns G, H, I and J, etc.

What I had up to this point is =IF(F3<1,(E3+90),""). Which works if the person is not discharged, but produces nothing if they are.

Thanks,
Dan

 

[GlennUK]

I don't see how you example works ( June 2, 2011 to June 2, 2011 with something every 90 days .... but that's a start and finish on the same day ). Provide some more examples of what you are aiming for.

 

[coolhanddan]

Thanks Glenn, my mistake. I meant to write, that if someone enrolls on June 2, 2011, their expected dates to complete requirements would be first 8/31/11, then again on 2/27/12, then 8/25/12. If they withdraw on 3/13, I want the formula to calculate the first two due dates, but to put in W if the withdraw date is before the expected due date, so in this case the cell would read 8/31/11, then 2/27/12, then W.

I want the formula to calculate a date 90 days from enrollment, if the withdrawal date is later than enrollment+90days. Then the same for enrollment +270 if withdrawal does not happen first, and etc for 450 days and 630 days.

I feel like I remember there was a for this that I used similarly once before, but I cant recall how to calculate dates based on information contained in more than once cell.

Thanks so much!

Dan

 

[barry houdini]

Hello Dan, welcome to MrExcel,

try this approach

=IF(F2<=E2+90,"W",E2+90)

then for the next, assuming the above formula is in E3

=IF(E3="W","W",IF(F3<=E3+180,"W",E3+180))

 

[coolhanddan]

Thanks so much Barry, its working for the +90, but for some reason not for 180 or 270.

Also, how do I get it not to insert a W for blank dates if the disenrollment cell is blank, to only calculate if there is a date entered in there?

Thanks,
Daniel

 

[barry houdini]

OK, try this for the first one

=IF(AND(F2<=E2+90,F2<>""),"W",E2+90)

then a similar change for the second one

=IF(E3="W","W",IF(AND(F3<=E3+180,F3<>""),"W",E3+180))

 

[coolhanddan]

Perfect!!!!!

Thank you so much! I really appreciate the help.

Dan

 

[coolhanddan]

I have another question on the same spreadsheet. I want to use my calculated dates, and in the column next to the projected due date, I have entered the actual date the form was completed. I want to be able to count the days between and if it is later than the projected date have it enter a 1, if it was early or on time, a 0. The way I am doing this now is I have a column subtracting say, g2 from f2, and then another column if h2>90 enter 1, if less enter 0. Is there a way to do this in one cell?