Index formula for Time + Date

Jack Parry

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")

1. How can I bring the time through as well as the date? (If I change the column to date, it brings the date through)
2. Is there a better way of doing this? Index + Match? VLookup?

Thanks in advance :)

Andrée Starå

@Jack Parry

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!

Andrée Starå

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!

Jack Parry

@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..

Andrée Starå

@Jack Parry

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!

Jack Parry

@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.

Andrée Starå

@Jack Parry

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!

Jack Parry

@Andrée Starå Worked perfectly! Thank you :)

Andrée Starå

@Jack Parry

Excellent!

You're more than welcome!