Excel formula to count number of days between sets of dates?

Hi all

I am building a staff training spreadsheet in Excel,
and would appreciate any help with finding a solution
to a date function query.

See attached spreadsheet for the exact layout that I have built with previous help
from the forums.

(On this spreadsheet, With help from the forums, I learned how to do COnditional Formatting,
the DATEIF function and check out the summary I designed in the tab "Monthly Summary".

Please feel free to use these bits yourself for anything you're working on. )


Now I need to add in two more bits.

The first is the the interval between training, this is just a number of days,
and is not a problem.

The second is the bit I need help with, I need to have a column called "DUE DATE"
and the due date of the training. The spreadsheet needs to add up the interval days
to the most recent date from DATE1, DATE2 or DATE3 and display the result, like this:

Does anyone know how to do this?

Thanks

Do you mean MAX(D6:H6)+J6 in column K (Due Date) to add the training interval to the most recent (maximum) training date?

Hi all

Thanks for the help with the spreadsheet query.

The formula =MAX(D2:H2)+J2 works nicely, but I have one
small issue, see attached file "Training II".

IF there is no data for the formula to calculate, as in Harley Quinn's
case, it returns a date like 01/01/1900.

Is there any way to solve this?

Could the MAX formula be altered to display a value like "OVERDUE" based on the
fact that there is no data to compute?

Thanks,

Barney

Try

=IF(MAX(D2:H2)=0,"OVERDUE ",MAX(D2:H2)+J2)

Hi all
Just to finish up this thread, I went with the DATEDIF solution, and it has worked flawlessly since before Christmas.

Here is the formula I used for a someone who has to do mandatory training every twelve months.

Anyone who has done the training in the last 11 months gets an "OK"

Anyone who has done the training in the last 11 months, but will be due to do the training within the next month
gets a "DUE"

Anyone who has never done the training, or has done it more than twelve months ago gets a "TO BE DONE"

=IF(DATEDIF(MAX(K101:O101),TODAY(),"m")<11,"OK",IF(DATEDIF(MAX(K101:O101),TODAY(),"m")=11,"DUE","TO BE DONE"))

Thanks again for the help.

Barney

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Hi all

Marked as SOLVED and updated my profile to Excel 2007.

Thanks again, great site!