CODEWITHSUNDEEP
exceldateexcel-dates
Faraz Masroor
Faraz Masroor

Reputation: 240

Add days to an Excel date?

In one cell I have the string "2023-Jan-02" formatted as a General. I want to simply add 7 days to these dates (so it goes to 2023-Jan-09), but I can't get the TEXT or DATE functions or cell formatting to work. How do I wrangle the formatting around to make this work?

enter image description here

Upvotes: 2

Views: 1317

Answers (5)

Zach McKinnon
Zach McKinnon

Reputation: 1

Here is some information on how to add to dates in Excel. You may just want to change the dates to numeric format, apply the addition of the days, then use a script or other formatting rule to change them back to words instead of just numeric values.

Upvotes: -1

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

This doesn't require any formula, to validate my statement, here is a demo:

enter image description here

  • Steps 1: Select the range and goto Data tab.
  • Step 2: Click on Text To Columns from Data Tools Group.
  • Step 3: In the Convert Text To Columns Wizard --> Step 1 --> Click nothing and leave as is, just select Next, --> Step 2 --> Click nothing and leave as is, just select Next, --> Step 3 --> Click on Date under Column Data Format --> Change it to YMD --> Click Finish
  • Since dates are stored as numbers in Excel it will return as a number and just convert it to the date format, you need.
  • Step 4: Write 7 in any empty cell.
  • Step 5: Copy the the cell where you have written 7.
  • Step 6: Select the date range converted using Text To Columns --> From Home Tab --> Click on Paste dropdown down and select Paste Special --> Paste will be Values and for Operations click on Add, and its done, you will see the required output.
  • Note that i have used shortcuts to show the process. ALT+A+E to open Text To Columns, and for pasting i have used ALT+H+V+S+V+TAB+A.

Using Formula perhaps one can use the following as well:

=--TEXTJOIN({" ",", "},,CHOOSECOLS(TEXTSPLIT(B1,"-"),2,3,1))+7

enter image description here

Upvotes: 0

Adam W.
Adam W.

Reputation: 179

If the date values are actually strings formatted to look like a date, you would need to convert it to a date first. However, there could be instances where it is already a date, so this formula will handle either situation and add 7 days.

=IFERROR(A1+7,
LET(dt,TEXTSPLIT(A1,"-"),
DATE(INDEX(dt,1),MONTH(INDEX(dt,2)&1),INDEX(dt,3)))+7)

A1+7 assumes it's an actual date and will try to add 7 days.

If it fails, the 2nd argument splits the values and in the DATE function, it pieces it back. The using &1 in the MONTH function will convert it a month name to number.

Original incorrect formula: =IFERROR(A1+7,DATEVALUE(A1)+7)

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Convert to a "real date" and add seven (7)

=DATEVALUE(TEXTJOIN("-",,(INDEX(TEXTSPLIT(A1,"-"),1,{3,2,1}))))+7

Upvotes: 2

M Foley
M Foley

Reputation: 11

Formulas Screensnip

It appears your dates are text and not dates. Assuming your text is in B2 I wrangle the pieces of your date using LEFT, MID, RIGHT, MONTH, DATEVALUE and the DATE function.:

C2: =LEFT(B2,4)
D2: =MID(B2,LEN("2023-X"),3)
E2: =MONTH(DATEVALUE(D2&"1"))
F2: =RIGHT(B2,2)
G2: =DATE(C2,E2,F2)
H2: =G2+7

Once you have an excel serial date adding 7 can be done with simple arithmetic. DATEVALUE will accept text in the format DD-MM-YYYY if you can instead get your input in that format. e.g.

=DATEVALUE("DD-MM-YYYY")

Upvotes: 1

Related Questions