Advice on Simplifying formulas

L_123

https://app.smartsheet.com/b/publish?EQBCT=3ae72b85648c4e968b5320160bb7e3c6

This sheet checks the net time machines were down, or work was done on a line item by using a date modified column and a date created column. The formula is rather complex, and I am not going to have the time to teach anyone how to edit it (they shouldn't have to, but stuff happens). Any ideas on simplifying it to a more readable format?I appreciate it.

(Partial Credit to Travis for creating one of the formulas)

L_123

https://app.smartsheet.com/b/publish?EQBCT=0b5b32e5763444658260d95091ad879f

this one is editable. 

CanadaJim

Hi Luke.  Did you get this resolved?  I can't access the sheets.

L_123

yeah i posted it yesterday. here i'll repost the link if you are interested

https://app.smartsheet.com/b/publish?EQBCT=77c33874736d40a78495cf4d4169365f

 

CanadaJim

Thanks.  First, I'd try to break down the dates and times from the data in the text fields.

You can get the date for each of the text fields by putting this formula in 2 Date columns:

=DATE(VALUE(MID(REPLACE(Created1, 6, 1, "/20"), 7, 4)), VALUE(LEFT(Created1, 2)), VALUE(MID(Created1, 4, 2)))

and

=DATE(VALUE(MID(REPLACE(Edited1, 6, 1, "/20"), 7, 4)), VALUE(LEFT(Edited1, 2)), VALUE(MID(Edited1, 4, 2)))

Now you can calculate the total hours downtime in a new Text/Number column.  I multiply the days difference by 24, add the hours difference (including the additional 12 hours if PM), and then divide the minutes difference by 60 to keep the units in Hours throughout.

=(VALUE(EditedDate1 - CreatedDate1) * 24)

+ ((VALUE(MID(Edited1, 10, FIND(":", Edited1) - 10)) + IF(RIGHT(Edited1, 2) = "PM", 12, 0)) - (VALUE(MID(Created1, 10, FIND(":", Created1) - 10)) + IF(RIGHT(Created1, 2) = "PM", 12, 0)))

+ ((VALUE(MID(Edited1, FIND(":", Edited1) + 1, 2)) - VALUE(MID(Created1, FIND(":", Created1) + 1, 2))) / 60)

Finally, we need to convert the hours of outage into the format you required in the final column.  Some rounding adjustment on minutes has been done due to some long real number strangeness in Smartsheets:

=ROUND((TotalInHours1 / 24) - 0.5) + "D "

+ ROUND((TotalInHours1 - ROUND(TotalInHours1 / 24 - 0.5) * 24) - 0.5) + "H "

+ ROUND((TotalInHours1 - ROUND(TotalInHours1 - 0.5)) * 60) + "M"

(just fixed a slight error on the hours portion)

The nice thing here is that it doesn't matter if it spans across AM/PM, minutes, hours, days, months or years... It'll account for all of that in the math.

Of course, if you're really ambitious, you could always combine the whole thing into a single formula.  It would make the formula much more confusing I think.

Jim

L_123

I didn't even think of reducing it to minutes in order to subtract the totals. Thanks!