Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Advice on Simplifying formulas
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)
Comments

https://app.smartsheet.com/b/publish?EQBCT=0b5b32e5763444658260d95091ad879f
this one is editable.

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

yeah i posted it yesterday. here i'll repost the link if you are interested
https://app.smartsheet.com/b/publish?EQBCT=77c33874736d40a78495cf4d4169365f

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

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