Formula : Date & Time Column

Laura

Can anyone assist me with a formula to show

Column 1 - Column 2 = Column 3? Sounds easy enough but the Date/Time column is throwing me off. This is an existing sheet so they don't want me to change the formatting on Column 1 or 2 if possible.

Column 1 : Created Date (Date & Time)

Column 2 : Modification Date (Date & Time)

Column 3 : Overall Days (Number of Days)

Paul Newcome

Are both columns 1 and 2 the system generated columns? If so, you can do a basic subtraction of one from the other to get the number of days.

Laura

@Paul Newcome well now I'm finding more obstacles within their sheet. Is there a way to record date and time when a checkbox is checked without using the modified date / time?

But to answer your question, yes its the system generated Date / Time Columns. Some are within the same day, some are several days apart. I've done the basic subtraction, and maybe I'm just overthinking it at this point but I'm not getting the formula correct to show me time in days / hours.

Paul Newcome

Here is a method I developed to do just that.

Once you get this set up, you can use an INDEX/MATCH or INDEX/COLLECT to pull that date/time stamp over and then you can use the newly released TIME function (link also below) to get the time difference.

Date/Time Stamp Option

Paul Newcome

Nov 21, 2019

While trying to figure something else out, I stumbled across a new option for having specific actions date/time stamped using the stem generated Modified (date) type column and the new Copy Row automation.

.

DISCLAIMER:

This does not scale very well as is. If you have a lot of rows you are wanting to track a lot of changes on, it can get very cumbersome very quickly.

My personal use for this solution is tracking a one-time change on each row (think "Completed" box gets checked).

.

Here's how it works...

I have the system generated Modified (date) column. I have another set of columns that separates the date from the time for further calculations, but you could even use a single extra column that simply duplicates the Modified column.

Set up the automation to trigger according to what you want to capture the date/time stamp for.

When a row is copied, only the data is copied. Not the formula. So whatever data is in the extra column that is formula driven to duplicate the Modified column will be pulled, but the formula to duplicate won't. It will also pull attachments and comments as well.

This effectively captures the date/time stamp for a specific action.

.

Yes. The date/time stamp is on a different sheet, but that just means that you can restrict access to the historical data.

Yes. It doesn't scale very well, but at least it is a step in the right direction and gives us an option to date/time stamp natively within SS.

---

View Post

TIME Function | Smartsheet Learning Center

https://help.smartsheet.com/function/time

Returns the time of day in 12- or 24-hour format from a given set of values.

Laura

@Paul Newcome this is VERY helpful! I cant believe I haven't discovered that amazing thread before now.

I've found a solution to everything except - I cant figure out how to get a time and date to auto record when a checkbox column is checked, without using the system modified date. Any ideas? Or is this in the Date/Time thread that I may have overlooked?

(if anyone makes a note in that same row the modified date will obviously change and give me incorrect data. The sheet is already using a Created Date for something else)

Paul Newcome

@Laura Here is a method I developed that allows you to capture a date/time stamp from the Modified column when a specific action occurs.

Once you get the below set up, you would use an INDEX/MATCH to pull it from the static sheet back to the working sheet.

Date/Time Stamp Option

Paul Newcome

Nov 21, 2019

While trying to figure something else out, I stumbled across a new option for having specific actions date/time stamped using the stem generated Modified (date) type column and the new Copy Row automation.

.

DISCLAIMER:

This does not scale very well as is. If you have a lot of rows you are wanting to track a lot of changes on, it can get very cumbersome very quickly.

My personal use for this solution is tracking a one-time change on each row (think "Completed" box gets checked).

.

Here's how it works...

I have the system generated Modified (date) column. I have another set of columns that separates the date from the time for further calculations, but you could even use a single extra column that simply duplicates the Modified column.

Set up the automation to trigger according to what you want to capture the date/time stamp for.

When a row is copied, only the data is copied. Not the formula. So whatever data is in the extra column that is formula driven to duplicate the Modified column will be pulled, but the formula to duplicate won't. It will also pull attachments and comments as well.

This effectively captures the date/time stamp for a specific action.

.

Yes. The date/time stamp is on a different sheet, but that just means that you can restrict access to the historical data.

Yes. It doesn't scale very well, but at least it is a step in the right direction and gives us an option to date/time stamp natively within SS.

---

View Post

Laura

@Paul Newcome **edited - I found one that worked.

=TIME(RIGHT(completed@row, 8))

should the formula below still work for extracting time from a created date/time column?

=IF(SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row) - 9), FIND(":", RIGHT(Created@row, LEN(Created@row) - 9)) - 1))) = 12, 0, SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row) - 9), FIND(":", RIGHT(Created@row, LEN(Created@row) - 9)) - 1)) * 60)) + SUM(VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2))) + IF(RIGHT(Created@row, 2) = "PM", 720, 0) + 1