# Converting Date to text add 1 Week

• I have a formula that i am trying to convert date to Text. I can do that fine but i cannot add a week to it. Thanks in advance

In Cell A1

=TEXT(U2,"mmm

dd

yy")

and Trying to in B1 I would like to write a formula that takes this date in Text format and add 1 week

=A1...+1 Week

• Hi Billy,

Not sure why you do not just reference U2 again rather than referencing the text, but you can try this formula:

=DATE(YEAR(MID(A1,4,2)&LEFT(A1,3)&RIGHT(A1,2)),MONTH(MID(A1,4,2)&LEFT(A1,3)&RIGHT(A1,2)),DAY(MID(A1,4,2)&LEFT(A1,3)&RIGHT(A1,2))+7)

Justin

• justin I do prefer to reference the text sorry if I did not explain properly. My intent is I will have to drag across for a chart. I do prefer to reference the text. Thanks for the assist

• You have spaces in your formula, I did not realise - modify to:

=DATE(YEAR(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,2)),MONTH(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,2)),DAY(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,2))+7)

and then to:

=TEXT(DATE(YEAR(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,2)),MONTH(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,2)),DAY(MID(A1,5,2)&LEFT(A1,3)&RIGHT(A1,2))+7),"mmm dd yy")

• Perfect thanks would not figured that out. Second guessing not referencing date. M

I figure out how to save a date in text like:

30

Mar

20

but when I use the date format:

30mar20 and could not separate. Thanks still working and it works now. Thanks

• You need to format the cell so that "wrap text" is turned on then use the formula:

=TEXT(U2,"mmm" & CHAR(10) & "dd" & CHAR(10) & " yy")

• Thanks appreciate the assist. I inherited a brief that already had 80+ charts that required updated. I was attempting to figure easiest path. Thanks for all the help.

• Cheers,

Don't forget to thumbs up helpful posts, it counts toward my points or something - which I am pretty sure I will be able to convert to bitcoin soon.

• Justin thanks this is working perfect. I've attached a file. I removed my charts for ease and space. My question is more about managing data and keeping formulas simple. You had mentioned why not reference date and I've been hung up on that. My attached file Converts a date to a text string. I did this because i was trying to meet an appearance on a chart. The format for the chart is as follows:

Month

day

Year

I cannot replicate this in no other way than as a text format. Do you think i should approach it in a different way.The formula works perfect but think i might have tackled in a different way. Again I'm moving forward and you advice and help is perfect.

Thanks for you help

## Files

• This formula achieves the same result in B5 and dragged across the range:

=TEXT(\$U\$2 + 7 * (COLUMN()-2),"mmm

dd

yy")

It is often better to post the scenario you are trying to achieve rather than assuming the solution, there are probably better formulas than this one, but it does the job.

• THis is perfect less cumbersome formula. And i do apologize on the clarity of my question. I have never seen "COLUMN" used before thanks for sharing.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!