Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

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

  • 02-12-2016 4:42pm
    #1
    barneysplash
    Closed Accounts Posts: 1,205 ✭✭✭


    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
    Tagged:


Comments

  • #2
    seamus
    Registered Users, Registered Users 2 Posts: 68,317 ✭✭✭✭seamus


    =MAX(D2:H2)+J2

    Put that in K2.

    Basically takes the most recent date in the range and then adds the interval to it, to give you the next date.


  • #3
    barneysplash
    Closed Accounts Posts: 1,205 ✭✭✭barneysplash


    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


  • #4
    riffmongous
    Moderators, Society & Culture Moderators Posts: 12,853 Mod ✭✭✭✭riffmongous


    Use an IF statement


  • #5
    barneysplash
    Closed Accounts Posts: 1,205 ✭✭✭barneysplash


    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


Advertisement