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
Did the formula do what you are after?
It did not. The date reference was Mar 30 20 and it returned a date of 29 jan 96
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
Justin is it possible to save a date format n a cell as
DD(return)
MMM(Return)
YY
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
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.
Don’t have an account yet? Register yourself now and be a part of our community!
