Results 1 to 9 of 9

Thread: Excel - Set up due date having the frecuency data

  1. #1
    Seeker bustos1juan's Avatar
    Join Date
    Mar 2019
    Posts
    7
    Articles
    0
    Excel Version
    2016

    Excel - Set up due date having the frecuency data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello dear excel professionalteam,
    I would like to get some help on solving an issue that I cannotfigure it out.
    I am building a sheet and I would like to identify the due datefor training having the frequency as my identifier for the due date. I meant,if "Bart" got the Fire training on 05/10/2016 and the traininghave a "Frequency" set up as "Every 3 years" I would likethat the column in "Due date" identify when is the date set up forthe next training as per the frequency defined in the Column"Frequency".
    I am providing my example attached. I have 5colums:
    Column WORKER (people that will take a training)
    Column TRAINING (The training available)
    Column START (When the training was taken first time)
    Column FREQUENCY (How often the training must be taken)
    Colum DUE (When the training must be taken once again)

    Each training has a frequency (Initial, Annually, Every 2 years and Every 3years) I would like to set up the due date column considering the frequencyrequired for each training.

    I hope you are kind on helping me with this issue.

    Best regards
    Jhon
    Attached Files Attached Files

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    137
    Articles
    0
    Excel Version
    2019
    What is the due date for initial? Leave blank?

  3. #3
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    137
    Articles
    0
    Excel Version
    2019
    In G7 type =IF(F7="Annually", EDATE(E7,12),IF(F7="Every 3 years",EDATE(E7,36),IF(F7="Every 2 years",EDATE(E7,24),""))) and copy down.

  4. #4
    Seeker bustos1juan's Avatar
    Join Date
    Mar 2019
    Posts
    7
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by alansidman View Post
    What is the due date for initial? Leave blank?
    Hi Alansidman,
    Yes, the due date for Innitial is "Cero", those are training that must be taken just once.

    I hope this help you!
    Tanks for taking your time with my issue.
    Jhon

  5. #5
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    137
    Articles
    0
    Excel Version
    2019
    In G7 type =IF(F7="Annually", EDATE(E7,12),IF(F7="Every 3 years",EDATE(E7,36),IF(F7="Every 2 years",EDATE(E7,24),"Cero"))) and copy down.

  6. #6
    Seeker bustos1juan's Avatar
    Join Date
    Mar 2019
    Posts
    7
    Articles
    0
    Excel Version
    2016

    Something missing

    Quote Originally Posted by alansidman View Post
    In G7 type =IF(F7="Annually", EDATE(E7,12),IF(F7="Every 3 years",EDATE(E7,36),IF(F7="Every 2 years",EDATE(E7,24),"Cero"))) and copy down.
    Thank you VERY MUCH. i just noticed something that I did not take into account. When a worker has not taken the initial or any of the other trainings the formula in DUE column says "error" and I would like to show "Immedistly" Is that possible for you to create that in the formula?

    Thanks once again for your time and support. I hope you can help me with this issue I did not condider.
    Jhon

  7. #7
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    137
    Articles
    0
    Excel Version
    2019
    So, what is in the column F (the Frequency) when this is the situation? Is it blank or is there something else that we could use as the trigger? This was not part of your original example.

  8. #8
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    137
    Articles
    0
    Excel Version
    2019
    Assuming that Column F is blank, then

    =IF(F7="Annually", EDATE(E7,12),IF(F7="Every 3 years",EDATE(E7,36),IF(F7="Every 2 years",EDATE(E7,24),IF(F7=" ", "Immedistly","Cero"))))

  9. #9
    Seeker bustos1juan's Avatar
    Join Date
    Mar 2019
    Posts
    7
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by alansidman View Post
    So, what is in the column F (the Frequency) when this is the situation? Is it blank or is there something else that we could use as the trigger? This was not part of your original example.
    Hello,
    You are right. It was not in my original exmple because when I put the formula I noticed that when column Start is blank the column DUE displays #VALUE!, the Column FREQUENCY will always display any of the options described (Initial, Annually, Every 2 years, every 3 years), your formula works perfectly, I just noticed and did not take into account that when cells in column START are blank then the formula in column DUE shows #VALUE!, and I would like when is the case that Column START is in blank then cell in column DUE display Immediatly.

    Thank you ones again for your time and support and I pologize for re asking about it, it is just I did not realize about the option of having in COlumn STRT cells in blank.

    I hope you can help me on it, please!
    Jhon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •