Extract length of time and subtract from date/time cell value

[RConner7]

I’ve been struggling with how to start and get this done. I have a column that contains a length of time that could be worded like: 53 seconds ago, 2 hours ago, 1 minute ago. Then I have a column that has the date/time the data was extracted.

I need to be able to subtract the column that has the length of time “ago” from the date/time the data was extracted to give me the exact date/time the row was added.

 

[Rick Rothstein]

I have a column that contains a length of time that could be worded like: 53 seconds ago, 2 hours ago, 1 minute ago.

Will the column ever have mixtures of those values? For example...

5 minutes and 24 seconds ago

1 hour and 43 minutes ago

2 hours, 15 minutes and 5 seconds ago

and so on.

 

[RConner7]

I've added some sample data to how it would look in the excel sheet. I would want to subtract the length of time within the 'SoldWhen' column from the Date/Time in the 'Time' column:

[TABLE="width: 400"]
<tbody>[TR]
[TD]SoldWhen[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]Updated 13 seconds ago[/TD]
[TD]3/17/2018 11:00:12 AM[/TD]
[/TR]
[TR]
[TD]Updated 20 seconds ago[/TD]
[TD]3/17/2018 11:00:12 AM[/TD]
[/TR]
[TR]
[TD]Updated 22 seconds ago[/TD]
[TD]3/17/2018 11:00:12 AM[/TD]
[/TR]
[TR]
[TD]Updated 23 seconds ago[/TD]
[TD]3/17/2018 11:00:12 AM[/TD]
[/TR]
[TR]
[TD]Updated a minute ago[/TD]
[TD]3/17/2018 11:00:12 AM[/TD]
[/TR]
[TR]
[TD]Updated a minute ago[/TD]
[TD]3/17/2018 11:00:14 AM[/TD]
[/TR]
[TR]
[TD]Updated a minute ago[/TD]
[TD]3/17/2018 11:00:15 AM[/TD]
[/TR]
[TR]
[TD]Updated 2 minutes ago[/TD]
[TD]3/17/2018 11:00:15 AM[/TD]
[/TR]
[TR]
[TD]Updated 2 minutes ago[/TD]
[TD]3/17/2018 11:00:16 AM[/TD]
[/TR]
[TR]
[TD]Updated 2 minutes ago[/TD]
[TD]3/17/2018 11:00:16 AM[/TD]
[/TR]
[TR]
[TD]Updated 43 minutes ago[/TD]
[TD]3/17/2018 11:02:41 AM[/TD]
[/TR]
[TR]
[TD]Updated 43 minutes ago[/TD]
[TD]3/17/2018 11:02:42 AM[/TD]
[/TR]
[TR]
[TD]Updated 49 minutes ago[/TD]
[TD]3/17/2018 11:03:06 AM[/TD]
[/TR]
[TR]
[TD]Updated an hour ago[/TD]
[TD]3/17/2018 11:04:04 AM[/TD]
[/TR]
[TR]
[TD]Updated an hour ago[/TD]
[TD]3/17/2018 11:04:04 AM[/TD]
[/TR]
[TR]
[TD]Updated an hour ago[/TD]
[TD]3/17/2018 11:04:05 AM[/TD]
[/TR]
[TR]
[TD]Updated an hour ago[/TD]
[TD]3/17/2018 11:04:05 AM[/TD]
[/TR]
[TR]
[TD]Updated 2 hours ago[/TD]
[TD]3/17/2018 11:09:34 AM[/TD]
[/TR]
</tbody>[/TABLE]

 

[RConner7]

Or... how can I conver the cells in that column from “26 seconds” to 0:00:26 and “25 minutes” to 0:25:00. Maybe that’s somewhere to start.