Index formula for Time + Date
Hi,
I'm using Smartsheets to build an external visitor log. Using one "Time In" form to capture the time in (using Created by column) and another to capture the time out (again using a created by column).
I then have a "Register" sheet where the time in row is copied to whenever an entry is added to the "Time In" sheet and have an index formula looking at the same row on the "Time out" sheet.
Date/Time Out:
=(INDEX({Visitors & Contractors Register - Out Range 1}, 1))
Pass Returned:
=IF(INDEX({Visitors & Contractors Register - OUT Range 2}, 1), "Yes", "No")
- How can I bring the time through as well as the date? (If I change the column to date, it brings the date through)
- Is there a better way of doing this? Index + Match? VLookup?
Thanks in advance :)
Best Answer
-
Excellent!
Yes.
To connect them row by row, you could use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you need in the Destination sheet.
I'd also recommend changing the INDEX/MATCH to:
=(INDEX({Visitors & Contractors Register - Out Range 1}, 0 ))+ ""
✅Remember! 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.
Answers
-
Hi @Jack Parry
I hope you're well and safe!
Try adding +"" at the end of the formula.
Did that work?
I hope that helps!
Be safe, and have a fantastic weekend!
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.
-
@Andrée Starå Unfortunately not:
=(INDEX({Visitors & Contractors Register - Out Range 1}, 1 + ""))
Also ran into another issue, when converting the column to formula its only looking at Row 1 and not the subsequent rows..
-
Try this.
=(INDEX({Visitors & Contractors Register - Out Range 1}, 1 ))+ ""
Did that work better?
✅Remember! 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.
-
@Andrée Starå Perfect!
However, is there a way to bring match row 1 to row 1 and row 2 to row 2. At present, the data for both rows is using row 2.
-
Excellent!
Yes.
To connect them row by row, you could use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you need in the Destination sheet.
I'd also recommend changing the INDEX/MATCH to:
=(INDEX({Visitors & Contractors Register - Out Range 1}, 0 ))+ ""
✅Remember! 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.
-
@Andrée Starå Worked perfectly! Thank you :)
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives