How to calculate elapsed time? (stopwatch / timer / timestamps)

[Blark]

I want to be able to take live notes during a video recording, and then be able to go back into the recording and find things easily.

More specifically, imagine a user:

1. Opens empty table

2. Types text into record 1, time is noted in adjacent cell as 00:00 (mm:ss)
   [it takes them 5 seconds to do this]

3. Types text into record 2, time is noted in adjacent cell as 00:05 (mm:ss)
   [it takes them 15 seconds to do this]

4. Types text into record 3, time is noted in adjacent cell as 00:20 (mm:ss)

And so on…

[ScottWorld · Accepted solution]

Airtable does have a built-in Time Tracker Block, but it won’t do what you’re looking for in this scenario. That’s because it starts over at 00:00 again whenever you create a new record. Although it does pick up where it left off on older records.

So, your best solution would probably be to simply add a “Created Time Field” to your table. Every time you create a new record, each new record will get the timestamp of when you created the record. it’s quick, easy, and foolproof.

Then, later on, when you are done with all of your notes, simply create a formula field that subtracts the ACTUAL START TIME of the video from your Created Time Field. That will get you the timestamps that you’re looking for.

A rough version of this formula would look like this:

DATETIME_DIFF({Creation_Date_Field},{Actual_Start_Time},'seconds')

The trick is to format your formula field to result in duration, which is based on seconds.

However, we wouldn’t want you to have a date field called “Actual_Start_Time” — we would want you to hardcode the actual start time into this formula. That’s because (a) it will be the same time for all of your records, and (b) you can’t easily put seconds put into a date field.

[kuovonne]

ScottWorld’s answer is the closest that you can do with Airtable alone. You can tweak the method a little by creating a “start” record and linking them to the rest of the records so that the formula fields for calculating intervals is a bit more automatic.

However, that method won’t work if you want to pause the video, rewind, or jump ahead.

You may be better off using transcription software to take your notes. The transcription software will make it easier to match up text with the recording, and then you can export the results from the transcription software into Airtable.

[ScottWorld]

Haha, yes, I was going to recommend the same thing as @kuovonne! That dedicated transcription software might be a better option for this!

In fact, a platform like Trint will even automatically transcribe AND timestamp your transcription for you, @Blark!

[Blark]

Thanks @kuovonne and @ScottWorld – those are great solutions. I’ll probably just calculate the elapsed time. So simple!

I just wish the ‘Date’ field recorded seconds – that’s what I was trying to use as my “Actual_Start_Time.”

[ScottWorld]

Glad that helped you! :slightly_smiling_face: If you don’t mind, could you please mark one of the comments above as the solution to your question? This will help other people who have a similar question in the future. :slightly_smiling_face:

[Blark]

@ScottWorld, I tried it out, and it works! Thanks!

@kuovonne Now I need to figure out a way to make it less manual as you mentioned. Currently I have to copy the Actual_Start_Time to each record one by one, otherwise they all get the same timestamp.

Here’s a 30 sec screen recording I made to illustrate:

I’d use transcription software, but there other things I want to be able to enter for records while taking notes.

[ScottWorld]

Great! Glad my answer worked perfectly for you! :slightly_smiling_face:

Here’s the trick to your most recent question:

Group your records by the “Actual Start Time” field.

Then, any new records that you create in that group will inherit the exact same “Actual Start Time”.

This is actually the ONLY way to auto-enter user-specified data for a date field, but it’s a pretty awesome trick. (Note that a few field types do have default auto-enter data options: number fields, single-line text fields, percent fields, and currency fields.)

[Blark]

That is an awesome trick, and it works! Now the issue is that the first record creates a timestamp of the actual clock time, not :00.

Here’s the formula I’m using:
DATETIME_DIFF(LAST_MODIFIED_TIME(),{Actual Start Time},‘seconds’)

I made a video of this:

[ScottWorld]

That’s showing you what you are grouping by. If you ungroup, that will disappear.