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

L_123
L_123 ✭✭✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

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

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

  • L_123
    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
    CanadaJim
    edited 08/16/17

    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
    L_123 ✭✭✭✭✭✭

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

This discussion has been closed.