Issue adding value to date field
I'm trying to populate a field that adds a value to a date to calculate a new date. However, the date field I'm trying to add to is a vlookup from a different sheet.
My formula is this:
=[Current Ex-Factory Date]@row + 63
The value under [Current Ex-Factory Date] is 10/26/22 so I would expect the result to be 11/05/22, however 10/26/2263 is what shows up.
If I key the [Current Ex-Factory Date] instead of the vlookup, the formula works as expected. Is there a workaround where I can keep the vlookup and avoid having the +63 treated as concatenate?
Answers
-
Hmm. This doesn't seem right and works fine when I test it.
So you have a vlookup that is bringing in a date to column "Current ex-factory date" and then a seperate column which adds 63 to this date?
Are all of your columns date columns?
I'm passionate about helping you leverage the truly awesome power of smartsheet!
-
Hi @BethC
I hope you're well and safe!
Ensure that the Current Ex-F... column is a date column.
Did that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Yes, all columns are date columns, even on the source sheet that the vlookup references.
-
Strange!
Can you maybe share some screenshots and the VLOOKUP formula you're using? (Delete/replace any confidential/sensitive information before sharing)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Here you go
Let me know if you have any other suggestions
-
I replicated it on my side, and it works!
I'd be happy to take a quick look.
Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I was able to fix the issue. It did come down to one of the columns being type text instead of date. Due to the vlookup range limitations within Smartsheets, I had to lookup to a sheet that was linked to the master sheet and that's where one of the columns was incorrect. Will be looking into DataTables to see if that will help with the vlookup limitation.
Thanks to all for your help!
-
Excellent!
You're more than welcome!
What VLOOKUP limitation are you thinking of? Also, I think you meant DataMesh.
✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
The 100,000 cell vlookup range limitation. No, I was referring to DataTable but I will look into DataMesh
-
Ok. The correct Premium App depends on your specific need. Among others, you have Data Shuttle, DataTable (that connects to Data Shuttle), and Data Mesh.
If you're hitting limits, I'd recommend replacing the VLOOKUP with INDEX/MATCH, if possible.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!