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




    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:



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


    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:




    but when I use the date format:

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

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




    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



    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.

Participate now!

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