Previous Topic Next Topic

Simple date subtraction excel

Solved
Eagleeyes011 Posts 29 Registration date Tuesday February 8, 2022 Status Member Last seen September 6, 2024 - May 21, 2024 at 04:27 PM
MasonFoster Posts 1 Registration date Wednesday September 25, 2024 Status Member Last seen October 7, 2024 - Oct 7, 2024 at 05:52 AM

-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:

5 responses

Answer 1 / 5
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 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 29 Registration date Tuesday February 8, 2022 Status Member Last seen September 6, 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
Answer 2 / 5
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 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
Answer 3 / 5
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
Answer 4 / 5
Roopan01 Posts 1 Registration date Thursday August 22, 2024 Status Member Last seen August 23, 2024
Aug 23, 2024 at 08:16 AM

Calculate the difference in days

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

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 5
MasonFoster Posts 1 Registration date Wednesday September 25, 2024 Status Member Last seen October 7, 2024
Oct 7, 2024 at 05:52 AM

Thank you so much for the information.
0

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses
Unable to change date format in Excel
rznbyz -
Tobby -

39 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses