Subtract from Date

0

How do you subtract 20 days from a date format that is in YYYYMMDD format. 

Sometimes it works if I do (date - 20) but if the date is less than 20 days it does not calculate correctly?

I need to have the data in column G  (=f2-20)

As you can see it errors out to "20160097" in the example. 

Answer
Discuss

Discussion

Please don't forget to select the answer that worked for you! Just click the Select Answer button at the bottom of the desired answer.
don (rep: 1492) Nov 25, '18 at 2:16 am
Add to Discussion

Answers

0
Selected Answer

 Use this formula to get the date format from a text cell that is formatted as YYYYMMDD

=DATE(LEFT(F2,4),MID(F2,5,2),RIGHT(F2,2))

F2 is the cell with the date. Once you do that, you should be able to perform normal date operations on the date.

Discuss

Discussion

It did not work. It changes the date to something way different..

It did convert the date back to MM/DD/YYYY Format - I need it to stay in YYYYMMDD format with no back slashes in the format.

For example
YYYYMMDD
20180322
Sroncey21 (rep: 28) Nov 20, '18 at 5:20 pm
There is a small typo in Don's formula. It should be MID(F2,5,2). Please try that.
Variatus (rep: 2165) Nov 20, '18 at 8:35 pm
Ah yes, I fixed the typo, thanks! Variatus is right in his answer that you should really be putting the date into a date format from the start soas to avoid issues like this and then play around with date formatting to get the look that you want. However, if you can't do that for one reason or another, my formula here will take the text value, put it into a date value, allow you to edit the date, and then, if you really want you can put it back into a string by concatenating the  YEAR(), MONTH(), and DAY() functions that you point to the new cell with the real date in it.
don (rep: 1492) Nov 21, '18 at 4:17 am
The formula works! Thank you so much! I just used a cell reference and formatted those cells for the YYYY/MM/DD format. Yes, the format is sort of silly but our programmer likes it that way, so that's the rationale for the workaround : )
Sroncey21 (rep: 28) Nov 25, '18 at 8:09 pm
Add to Discussion
0

There seems to be a problem with the way you enter the date, not the date itself. Please do this test.

  1. In a blank cell enter a date using the date format which is defined as Short Date in your International Settings (for Windows).
  2. Now click on Format Cells. You will see that Excel assigned a Date format to the cell.
  3. Now assign the General format to the cell. The cell value will change to a 5-digit number. For March 22, 2018 it would be 43181.
  4. With this number you can do calculations, like = F2+20.
  5. You can also assign any date format to the cell and can still do the calculations because the underlying value remains the same irrespective of format.

It's only when you try to outsmart Excel and enter '20160322 (with the leading apostroph) or otherwise change the cell format to Text before entering anything into it that Don's formula will work. You should not use that way if you have control over what to enter in the cell. Obviously, just entering a date would be easier than entering text and converting the text to a date, which is what Don's formula does.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login