Excel - Set up due date having the frecuency data

bustos1juan

New member
Joined
Mar 21, 2019
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016
[FONT=&quot]Hello dear excel professionalteam,[/FONT]
[FONT=&quot]I would like to get some help on solving an issue that I cannotfigure it out.[/FONT]
[FONT=&quot]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".[/FONT]
[FONT=&quot]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[/FONT]
 

Attachments

  • How to set up Due date with specific date.xlsx
    14.8 KB · Views: 12
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.
 
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
 
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.
 
Something missing

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
 
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.
 
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"))))
 
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
 
Back
Top