Read Date in Cell D7 to Determine Date in Cell G7

[reberryjr]

I often struggle with formulas that involve dates, and this one is no different. If the value in C7 = "Annually" and the date in D7 is less than 12/01/2023, I want to update the value of G7 to 12/01/2023. Otherwise, if the value in C7 = "Annually" and the date in D7 is greater than 12/01/2023, I want to update the value of G7 to 12/01/2024. I already have this formula in column G, so I'm hoping I don't need to next 11+ other IF statements to do this.

=IF(C2="Adhoc","Adhoc",IF(C2="Daily",D2+1,IF(C2="Weekly",D2+7,IF(C2="Monthly",EDATE(D2,1),IF(C2="Quarterly",EDATE(D2,3))))))

 

[Legacy 143009]

Yes you will need another if

=IF(C2="Adhoc","Adhoc",IF(C2="Daily",D2+1,IF(C2="Weekly",D2+7,IF(C2="Monthly",EDATE(D2,1),IF(C2="Quarterly",EDATE(D2,3),IF(C7="Annually",IF(D7<DATEVALUE("12/01/2023"),DATEVALUE("12/01/2023"),DATEVALUE("12/01/2024"))))))))

 

[reberryjr]

Yes you will need another if

=IF(C2="Adhoc","Adhoc",IF(C2="Daily",D2+1,IF(C2="Weekly",D2+7,IF(C2="Monthly",EDATE(D2,1),IF(C2="Quarterly",EDATE(D2,3),IF(C7="Annually",IF(D7<DATEVALUE("12/01/2023"),DATEVALUE("12/01/2023"),DATEVALUE("12/01/2024"))))))))

I appreciate the response, and it works. However, I was hoping that the formula would automatically update as time goes on. Example: G2 should read 12/1/23. Once the User makes a separate update, they will update D2 to let's say 12/5/23. G2 should automatically update to 12/1/24. Then next year, they perform an update and enter 12/4/24 in D2; where G2 would automatically update to 12/1/25

 

[Legacy 143009]

How about:

=IF(C2="Adhoc","Adhoc",IF(C2="Daily",D2+1,IF(C2="Weekly",D2+7,IF(C2="Monthly",EDATE(D2,1),IF(C2="Quarterly",EDATE(D2,3),IF(C7="Annually",IF(D7<DATE(YEAR(TODAY()), 12, 1),DATE(YEAR(TODAY()), 12, 1),DATE(YEAR(TODAY())+1, 12, 1))))))))

 

[reberryjr]

How about:

=IF(C2="Adhoc","Adhoc",IF(C2="Daily",D2+1,IF(C2="Weekly",D2+7,IF(C2="Monthly",EDATE(D2,1),IF(C2="Quarterly",EDATE(D2,3),IF(C7="Annually",IF(D7<DATE(YEAR(TODAY()), 12, 1),DATE(YEAR(TODAY()), 12, 1),DATE(YEAR(TODAY())+1, 12, 1))))))))

Unfortunately, that still doen't meet the need as it requires an actual date to be entered in the formula. However, your proposal helped me figure out a different solution. The only thing that will be listed as an Annual update is holiday dates for use in Networkdays formulas. So, here is the complete formula I will be using. Thanks again for your help.

=IF(C7="Adhoc","Adhoc",IF(C7="Daily",D7+1,IF(C7="Weekly",D7+7,IF(C7="Monthly",EDATE(D7,1),IF(C7="Quarterly",EDATE(D7,3),IF(C7="Annually",DATEVALUE("12/01/"&MAX(YEAR(Holidays!A:A)))))))))