Simple date subtraction excel Solved

Question

-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)"

vcoolio · Answer

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.

vcoolio · Answer

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

Cheerio,

vcoolio.

Tbonekiller · Answer

=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.

Roopan01 · Answer

Calculate the difference in days

Select cell D2, which is the first blank cell in the Duration column.
	Type =C2-B2, and then press RETURN . Excel displays the result as the number of days between the two dates (104).
	Select cell D2.
	To copy the formula into the remaining rows, drag the fill handle .

MasonFoster · Answer

Thank you so much for the information.

Simple date subtraction excel

5 responses

Similar discussions

Subscribe To Our Newsletter!