How to create a formula to calculate the time after a row is added to a sheet?

Rhianna Johnson

I need the time from when the row is added to the sheet until the status column is modified calculated.

The reason I am needing this formula to calculate the time is, I need to notify management once the time has reached 60 minutes only if the status cell that has a drop down box has NOT changed from the "not started" state within the 60 minutes of placing the row into the sheet.

Jeremy C

I think you can achieve this by adding a few columns:

1 column with the column type 'Created Date' (this is a static date and can not be changed)

a 2nd column as the 'Modified Date' (this can be tricky if the row will be modified multiple times)

and a 3rd column to formulate the difference between the 2.

Additionally you could setup a workflow to automatically notify management when the 3rd column is greater than 60mins.

Hope this helps.

Rhianna Johnson

I am trying to create a Material work flow for our Warehouse. The production floor will request the material through a form, when the appropriate warehouse personnel receive this request they will change the status to in progress when picking the order. The status column in that row will have a drop down menu of Not Started, In Progress, Complete, or Out of Stock. They will also have other columns to fill in for the material requested. "Amount issued", "Lot Number", "Location Issued", "Initials". Also on this same sheet on the right side as this is a split sheet when the material comes back in they will fill out a Material return form that will dump into this same sheet as well.

I have been able to create a formula to pull only the time from the the "Created Date" column.

KPH

Following Jeremy C's note above, I wrote full instructions for the 3 columns you need. But once I finished, and re-read your first question, I realized this might not help. However, it might help someone with the same question so here is how to calculate the time between Created Date and Modified Date. I will send another answer below.

1 - Created date - that you have.

2 - for the second column you can use the "Modified Date" field type to capture the date and time the row was last edited. Like @Jeremy C says this could be a problem if the row is modified multiple times, but for now, let's assume that it isn't.

3 - The column to count the minutes between the two columns.

I am going to assume we don't need to worry about tasks going over midnight and am going to break this into 3 columns (all text) because the formula are quite long.

Each formula should be converted to a column formula (I haven't here as I like to share the screen shots with the color coding) - just right click on the cell and choose the "Convert to Column formula" option when you're done.

3a Created Time - to separate the time from the date

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

3b Modified Time (the same formula just on a different cell)

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

3c Difference between these

=IF([Created Date]@row < [Modified Date]@row, SUM(1440 - [Created Time]@row) + [Modified Time]@row, [Modified Time]@row - [Created Time]@row)

Ignore my Diff 2 column - I was trying to see if I could simplify this. I could not!

KPH

Now for a different question. How to find out the time 60 minutes after a created date (because if the warehouse hasn't changed the status from "Not Started" there will not be a modified date so the option above won't work). However, this is not a full solution as I can't figure out how to send an alert once we know when it is due. Maybe someone smarter than me can finish that.

1. For this we are just using the Created On column you have already

2. Then add a CreatedTime column where we separate the time from the date

=RIGHT([Created On]@row, 8)

3. Then an Ugly Alert Time column to add 60 minutes to this time

=IF(VALUE(LEFT(CreatedTime@row, FIND(":", CreatedTime@row) - 1)) <> 12, IF(CONTAINS("p", CreatedTime@row), 12), IF(CONTAINS("a", CreatedTime@row), -12)) + VALUE(LEFT(CreatedTime@row, FIND(":", CreatedTime@row) - 1)) + (VALUE(MID(CreatedTime@row, FIND(":", CreatedTime@row) + 1, 2)) / 60) + 13

(I have time zone issues so you might need to adjust this)

4. Finally the prettier alert time

=MOD(INT([Ugly Alert Time]@row), 12) + ":" + IF(([Ugly Alert Time]@row - INT([Ugly Alert Time]@row)) * 60 < 10, "0") + ([Ugly Alert Time]@row - INT([Ugly Alert Time]@row)) * 60 + IF([Ugly Alert Time]@row >= 12, " PM", " AM")

Hoping this is at least a start and someone can pick it up from here.