Simple date subtraction excel

Solved
Eagleeyes011 Posts 27 Registration date Tuesday February 8, 2022 Status Member Last seen May 28, 2024 - May 21, 2024 at 04:27 PM
Tbonekiller Posts 17 Registration date Wednesday August 21, 2019 Status Member Last seen June 6, 2024 - Jun 6, 2024 at 01:44 PM

-It seems soo simple, yet I haven't been able to find the solution. I need to display a date that is five years in the past from today.

-I tried a couple of formulas with no success, =days(-1826) and =days(today(), -1826). Daysif, days360, datevalue... etc.

-I've tried searching for a solution with no success. I chose 1826 days as that will incorporate at least 1 leap year, although the 5-year period will occasionally include 2 leap years. 

-This is the tricky part, I would like it to display in the header if possible. I understand there may be rules that need to be followed and that it may not work there. But that's my perfect world solution. I'm understandable that it may not work that way. This will be displayed on a single worksheet in a multi-sheet workbook in excel. (if vcoolio answers this, it's on the historical worksheet you helped me with). I can always find another location to place the code.

-I also need the verbiage of, "Delete if older than (display date 5 years before today)"

Related:

3 responses

vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
May 22, 2024 at 12:44 AM

Hello again Sean,

You may only need something like this:-

=DATE(YEAR(TODAY()) - 5, MONTH(TODAY()), DAY(TODAY()))

to display a date from five years ago.

Test it in any cell within your workbook.

I hope that this helps.

Cheerio,

vcoolio.

1
Eagleeyes011 Posts 27 Registration date Tuesday February 8, 2022 Status Member Last seen May 28, 2024 1
May 22, 2024 at 08:24 AM

vcoolio, that did it. Thank you. I was not able to use the formula in the header.

-Turns out the header has a particular purpose that does not allow formula's to be added. I could create a macro that would enter the data from the formula, but I want that date to update automatically without a macro. If there's a way to do that I don't know it, and honestly don't need it. I just added a row above the top and used it. As always, thank you! 

0
vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
May 23, 2024 at 01:20 AM

You're welcome Sean. I'm glad to have been able to assist again.

Cheerio,

vcoolio.

0
Tbonekiller Posts 17 Registration date Wednesday August 21, 2019 Status Member Last seen June 6, 2024
Jun 6, 2024 at 01:44 PM

=IF(INT(YEAR(G1)/4),+G1-(5*365)-2,+G1-(5*365)-1)

G1= creation date of file or =now()

I haven't seen your other file, but if you have a master sheet with all the creation dates then you would just add a column for "removal" date.

Just depends on how you want to use it. Just thought I would give you an alternate version just in case.

0