Formula : Date & Time Column

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)

Find more posts tagged with

Accepted answers

All comments

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.

https://community.smartsheet.com/discussion/63561/date-time-stamp-option

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

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.

https://community.smartsheet.com/discussion/63561/date-time-stamp-option

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.