Date formula

[Sawdeeka01]

Hi,

I am hoping you could assist me please. I have two (2) queries I need some help with.

The first query is that I need selected cells to auto populate based on the Nomination Date (K14).

I need Outcome Date (K15) to auto populate based on the date populated in K14 and plus 28 days;
I also need Assessment Date (K16) to auto populate based on the date populated in K14 and minus 365 days.
I would also like the cell to be blank if column K14 is blank if possible?


I have been trying to use the below formula however it will not populate if the date range is in December 2020 for some reason;

=IF(AND((TODAY()-28)>K14,K14<>""),K14+28,"")

My second query is that I need to come up with a formula that will lock a cell, if a certain text is selected from a drop down list in another cell....

Employment Status (K17) has a drop down list with the following options; Casual or Temporary. I need to be able to lock CAS_REG (K26) if Temporary is selected from the drop down list. If Casual is selected I need CAS_REG (K26) to remain open.

I hope I haven't confused anyone and welcome and am very thankful for any assistance you could offer me!

Sawdeeka01

 

[Peter_SSs]

Try these ..
K15, Formula: =IF(K14="","",K14+28)
K16, Formula: =IF(K14="","",K14-365)
K26, apply Data Validation -> Custom: =IF(K17="Temporary",LEN(K26)=0,LEN(K26)>0)

 

[Sawdeeka01]

Try these ..
K15, Formula: =IF(K14="","",K14+28)
K16, Formula: =IF(K14="","",K14-365)
K26, apply Data Validation -> Custom: =IF(K17="Temporary",LEN(K26)=0,LEN(K26)>0)

Hi Peter,

Thank you so much for taking the time to put the above together for me, they all worked perfectly! You make it all look so easy.

Have a great day,

Sawdeeka01

 

[Peter_SSs]

You're welcome. Glad they worked for you. Thanks for the follow-up.