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
-
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
Answers
-
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
-
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!
-
@Nick Amsler just add 14 days to your date in your formula as shown above...
=[XX Date]@row + " - " + ([XX Date]@row + 14)
-Ryan
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!