formula for data base

Question

i am very new to formulas, so be nice. Okay, I have a cell which either as ATTENDED - DNA - WITH A DATE THEY EITHER DID OR DI NOT ATTEND.SO LIKE THIS IN CELL H DNA - 11/12/24

I WANT TO ADD JUST THE DATE IN CELL G FOR FOLLOWING YEAR ,THIS WILL GO DOWN ABOUT 150 PATIENTS I HOPE HIS MAKES SENCE, Thank you

EDATE ,it works when only a date is used , but because it as a text in i cannot get it to work. =EDATE(HE,12)

• automatic-updates

Answer 1

Hi @Debbie Lester

Welcome to Q&A.

For better analyze, could you please share us with a simple sample?

• Do you mean the data in cell H looks like "DNA - 11/12/24"?
• You want to add the date in cell G for following year, am I right?

If so, I suggest you try the following formula in cell G:

Explanation:

• Extract date: =TRIM(MID(SUBSTITUTE(H1,"-",REPT(" ",100)),100,100))
• Convert the data from text to date format: =DATEVALUE(TRIM(MID(SUBSTITUTE(H1,"-",REPT(" ",100)),100,100))), please do not forget to format cell in Date format.
• Add 1 year: =EDATE(DATEVALUE(TRIM(MID(SUBSTITUTE(H1,"-",REPT(" ",100)),100,100))),12), please also do not forget to format cell in Date format.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---