Maintaining a date value through a formula
Hi,
I am using a form to collect an employee Title and the start date for that employee. There are three different titles, and a different columns to collect that date per title (to allow us more instructions in the form that is being filled out). Ideally I'd like to keep the start date separate for each title. However, once they are in the sheet, for automation and some reporting purposes, I'd like to combine the dates into one column. I used JOIN to do that in the "Start Date" column, however that does not maintain the date value. Not sure if Index would be better? Is there a way to have the "Start Date" column recognized as a Date column, while using a formula to pull the data from other columns?
In summary, I want a formula in the "Start Date" column that says, If the "Title" is Title 1, display the date from "title 1 Start Date", if the "Title" is Title 2, display the date from "title 2 start date", if the "title" is Title 3, display the date from "title 3 start date". And have the answer recognized as an actual date.
THANK YOU!!
Best Answer
-
Try this...
=INDEX(COLLECT([Title 1 Start Date]@row:[Title 3 Start Date]@row, [Title 1 Start Date]@row:[Title 3 Start Date]@row, @cell <> ""), 1)
Answers
-
Try this...
=INDEX(COLLECT([Title 1 Start Date]@row:[Title 3 Start Date]@row, [Title 1 Start Date]@row:[Title 3 Start Date]@row, @cell <> ""), 1)
-
PAUL! That did it! I cannot thank you enough- really. The amount of hours I have put into trying to make this work, and the amount of time spent creating work arounds in the meantime has been so frustrating. This is going to fix so much -thank you!
-
Happy to help. 👍️
There is another option that is a little more straightforward but requires more typing. It is basically a nested IF based solution where you say if the first one is not blank then output the first one, if the second one is not blank then output the second one, so on and so forth.
-
Thanks for the direction with that- I'm always trying to understand these formulas better so I'll try it out!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!