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!