simple date range question

Hello,

I feel silly for asking this, so I hope this is easy. I cannot find a simple answer on this anywhere. Every question I read date ranges is about some complicated calculations with other data etc..

I simply want to have a formula that either combines two dates to show a range or calculate a range based on just adding 2 weeks. I have two columns that have a date and simply want to create a column that shows "dd/mm/yy - dd/mm/yy" with the second date being two weeks from the first date.

I have already created the second column to be =[XX Date]@row + 14. But I can't seem to get these two dates to simply just show a respective range in a third column. If I select the two columns and use the "-" symbol between it does a calculation instead. I have also tried =TEXT([XX Date]@row - [XXX Date]@row) but it doesn't work either.

Do I need to take it out of a date format and just use text? Any help with this would be appreciated as I can't seem to find the right formula to make this look like an actual range of dates.

Thanks in advance!

Best Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    Answer ✓

    Hi @Nick Amsler,

    Once you use a double quote in a formula, your date becomes part of a string (text).

    I just tested this formula against two date column I have, and it worked:

    =([Target Start Date]@row + " - " + [Target End Date]@row)

    **Modify the column names to your own.

    Here is the result:

    03/13/23 8:00 AM - 03/17/23 4:59 PM

    You could get fancy with it if you wanted to parse out the times.


    Hope this helps!

    All the best,

    -Ray

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓

    @Nick Amsler

    Hey Nick, you were close! Give this a try...

    =[XX Date]@row + " - " + ([XX Date]@row + 14)

    Lemme know if that works for you!

    -Ryan

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Answers

  • Ray Lindstrom
    Ray Lindstrom ✭✭✭✭✭✭
    Answer ✓

    Hi @Nick Amsler,

    Once you use a double quote in a formula, your date becomes part of a string (text).

    I just tested this formula against two date column I have, and it worked:

    =([Target Start Date]@row + " - " + [Target End Date]@row)

    **Modify the column names to your own.

    Here is the result:

    03/13/23 8:00 AM - 03/17/23 4:59 PM

    You could get fancy with it if you wanted to parse out the times.


    Hope this helps!

    All the best,

    -Ray

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓

    @Nick Amsler

    Hey Nick, you were close! Give this a try...

    =[XX Date]@row + " - " + ([XX Date]@row + 14)

    Lemme know if that works for you!

    -Ryan

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Nick Amsler
    Nick Amsler ✭✭✭✭

    Thank you both @Ray Lindstrom and @Ryan Sides , I believe I was missing the extra plus sign on the other side of the quotes, because I really did try the quoted "-" too. Your solution worked. Now I wonder if there is a way I can eliminate the need for the second column and simply create a range from just one date + 2 weeks. Either way at least I have a solution. Thank you so much!

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭

    @Nick Amsler just add 14 days to your date in your formula as shown above...

    =[XX Date]@row + " - " + ([XX Date]@row + 14)

    -Ryan

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!