Date formula

[Sawdeeka01]

Hi,

I am hoping you could assist me please. I have a data set that I need to manipulate and automate for ease of use moving forward. Below is a snippet of what I am working with.

Essentially, I need to take the 'QH Comm Date' (column A) and keep the DD/MM but change the YYYY to 2023 in the 'Anniversary Date' (column B). So the Anniversary date for A2 would be recorded as 30/09/2023 in B2. This would allow me to have an accurate recording in 'Days Until Anniversary Date' (column C), which is a formula counting the difference between 'Days Until Anniversary Date' and 'Today's Date' (column D).

Is this something you could assist me with? I am sorry I did look over existing posts but I really don't know what to even call what I am trying to achieve. I appreciate your time and effort,

Regards,

Roxanne

 

[Peter_SSs]

Try this formula in B2. Where I have A15 in the formula (twice) put the last cell in column A that has a date.

=DATE(2023,MONTH(A2:A15),DAY(A2:A15))

 

[Sawdeeka01]

Try this formula in B2. Where I have A15 in the formula (twice) put the last cell in column A that has a date.

=DATE(2023,MONTH(A2:A15),DAY(A2:A15))

Hi Peter,

Thank you for your prompt response and suggestions. I have applied that formula and included the last cell that the date is recorded on the main data set however it is returning #SPILL!

 

[Sawdeeka01]

Hi Peter,

Thank you for your prompt response and suggestions. I have applied that formula and included the last cell that the date is recorded on the main data set however it is returning #SPILL!

Its working now, thank you so much!!!

 

[Peter_SSs]

You're welcome. Glad to help. As you have discovered, the formula only needs to go in a single cell.