Calculating a Due Date

Jerry Tutt

Frequently in legal transactions a transaction closing date may depend on the outcome of a couple or more previouls tasks.  It will be the later of two or more previous tasks.  Each task's completion date plus a different number of days depending on the task.  The answer is the latest plus another specified number of days.  The problem is that you cannot place a formula in an End Date.  Well, you can if you create a new "Date Column" which becomes your calculated "End Date".  You can place your formula in the cell that needs to be calculated based on the previous two End Dates.  In the other cells that don't need any additional calculations you can just make them equal to the normal End Date cell in its row (=[Due Date]5 ).  The column that contains the normal calculated end date is hidden and the new date column becomes your End Date.  Maybe no one needs to do this and maybe I am making it way to complicated.  But, I think it is going to be very useful in our situation.  If anyone is interested in this let me know and I can enter the formula.  

Kennedy Stomps

Great solution, Jerry!

Jerry Tutt

The following is the formula for the Closing Date.   Complete9 and Complete 17 are the two previous tasks that must be completed prior to closing.  The Complete column is hidden.  The formula is placed in the new column that I called Due/Complete Date.  In any cell where the calculated date doesn't need to be modified you can just place a small formula (=CompleteN where N is the row number) to simply equate to the hidden date.  This is very useful because in the legal profession (depending on the legal contract) many tasks cannot complete on a weekend date, however, calendar days are needed for most of the other date calculations.  Therefore, you then need a formula to boost the date to a Monday if it falls on the weekend.  

 

=IF(((Complete17) + 30) > ((Complete9) + 7), ((Complete17) + 30), ((Complete9) + 7))