Formulas for Calculating Time

11213141517

Comments

  • Update!

    I think I have managed to overcome it, still testing though:

    It required some additional helper column:


    Start_Day_Number

    =WEEKDAY([Start Date]@row) - 1 - for EU

    End_Day_Number

    =WEEKDAY([End Date]@row) - 1 - for eu

    6 = Saturday

    0 = Sunday

    Weekend_Check

    =IF(AND([Start_Day_Number]@row = 6, [End_Day_Number]@row < 2, [SUM_Days]@row < 2), "WEEKEND ONLY", IF(AND([SUM_Days]@row > 7, NETWORKDAYS@row >= 6), "TWO WEEKENDS", IF(AND([End_Day_Number]@row < [Start_Day_Number]@row, [SUM_Days]@row > 2), "WEEKEND", IF(AND([Start_Day_Number]@row = 5, [End_Day_Number]@row = 6, [SUM_Days]@row < 2), "SATURDAY ONLY", IF(AND([Start_Day_Number]@row = 0, [SUM_Days]@row > 2), "SUNDAY ONLY", "NO")))))


    SLA_Time

    =IF(OR([Weekend_Check]@row = "TWO WEEKENDS", [Weekend_Check]@row = "WEEKEND ONLY", [Weekend_Check]@row = "SUNDAY ONLY"), NETWORKDAYS@row, IF([Weekend_Check]@row = "WEEKEND", [SUM_Days]@row - 2, [SUM_Days]@row))


    Its not great but humble work, as for now it works. Will update if any troubleshooting will be required.


    Thanks,

    Roman

  • richard_abra
    richard_abra ✭✭✭✭

    This is exactly what i need. can you summerise all your posts so i can try to replicate? im confused with all the updates and dont want to use the wrong one. thanks!

  • Hey @richard_abra

    Thanks, I will try and let you know!

  • richard_abra
    richard_abra ✭✭✭✭

    Very kind. I spent most of last night and this morning trying but I can’t get the formula to work. I’ve check and triple check. Also cell linking. I can manually do it! So somethings working 😂

  • What do you want to achieve to be precise, so it will be easier to help, with a screenshot?

  • richard_abra
    richard_abra ✭✭✭✭
    edited 02/07/24

    The ability to be able to cross reference my quoted time it takes to do a job inc each process within that job. The ability to be able to start, Pause and stop from a form a timer and then link that actual value to what we estimated on the quote (also done in smart sheet) including taking weekends and none work times into consideration . this is ideal for me but anything for now is better than nothing!

  • allison.worley
    allison.worley ✭✭✭✭

    I have loosely reviewed all 17 pages. I asked this question in a different way in my own post but have changed how I am calculating. For reference I can calculate the time in hours to a decimal as long as the time occurs within the same 24 hours. See below.

    I then tried to change the formula over to incorporate the date noting that we are using a HH:MM:SS format in military time so no AM PM is present. I have changed the below many times moving brackets, commas, etc. For a while I had

    I have changed the below many times moving brackets, commas, etc. For a while I had #InvalidValue below. What am I doing wrong?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @allison.worley To calculate when the dates are different, you would use a slightly different logic. Basically you want to calculate the difference in time between the start time and midnight. Then you want to get the end time as a number. Next you would add up the number of days, subtract 1 and multiply by 24. Finally you would add all three of those numbers together.

    =(24:00:00 - Start Time) + (End Time as a number) + ((End Date - Start Date - 1) * 24)

    You would then use an IF statement to incorporate this with your first formula that works for when the start and end dates are the same.

    =IF([Start Date]@row = [End Date]@row, current formula for same day, formula for different days)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Jim B
    Jim B ✭✭✭

    Hi folks,

    Are you working with shot lists / EDLs / media clocks?

    I have a copy/paste formula here for anyone who needs to calculate frames where you have a TC in and a TC OUT in 00:00:00:00 format (HH:MM:SS:FF). You'll need to add a column for TC IN and TC OUT obviously.

    =((VALUE(MID([TC OUT]@row, 1, 2)) * 9000) + (VALUE(MID([TC OUT]@row, 4, 2)) * 1500) + (VALUE(MID([TC OUT]@row, 7, 2)) * 25) + (VALUE(MID([TC OUT]@row, 10, 2))) - ((VALUE(MID([TC IN]@row, 1, 2)) * 9000) + (VALUE(MID([TC IN]@row, 4, 2)) * 1500) + (VALUE(MID([TC IN]@row, 7, 2)) * 25) + (VALUE(MID([TC IN]@row, 10, 2)))))

    Jim

  • allison.worley
    allison.worley ✭✭✭✭
    edited 06/25/24

    @Paul Newcome

    =IF([Start Date]@row = [End Date]@row, (TIME([BOT End Time]@row) - TIME([BOT Start Time]@row)) * 1440 / 60, IF([End Date]@row > [Start Date]@row, (24:00:00 - Start Time) + (End Time as a number) + ((End Date - Start Date - 1) * 24)))

    I know this isn't right because it came out #unparseable but can you help me string together?

    =IF([Start Date]@row = [End Date]@row, (TIME([BOT End Time]@row) - TIME([BOT Start Time]@row)) * 1440 / 60, (24:00:00 - Start Time) + (End Time as a number) + ((End Date - Start Date - 1) * 24))

    still #unparseable

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @allison.worley My formula was not a copy/paste solution. it was simply a concept to show you where things go. Whatever formula you are currently using to convert times into numbers would be used to convert 24:00:00 into a number and then subtract the start time from it then add the portion of your formula that is converting the end time into a number. You would also need to use whatever column names you are using in your sheet to include for the start and end date columns.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • allison.worley
    allison.worley ✭✭✭✭
    edited 06/25/24

    @Paul Newcome

    =IF([Run Date]@row = [Run End Date]@row, (TIME([BOT End Time]@row) - TIME([BOT Start Time]@row) * 1440 / 60, (24:00:00 - [BOT Start Time]@row) + ([BOT End Time]@row) + (([Run End Date]@row - [Run Date]@row - 1) * 24)))

    So, something like this but the one in bold would have to be a further formula as it would =need to be changed into a decimal. This is so above my skill set as #mathishard lol

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The part in bold as well as the 24:00:00. An example for the part in bold would be what you are already using to leverage the end time.

    =IF([Start Date]@row = [End Date]@row, (TIME([BOT End Time]@row) - TIME([BOT Start Time]@row) * 1440 / 60, (24:00:00 - [BOT Start Time]@row) + (TIME([BOT End Time]@row)) + (([Run End Date]@row - [Run Date]@row - 1) * 24)))

    It looks like you have other formulas as well that are converting the HH:MM:SS into numbers such as as where you have the VALUE(LEFT(…………. You can use something like that on 24:00:00 or you can already know that 24 is the hour, there are no minutes, and there are no seconds.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • allison.worley
    allison.worley ✭✭✭✭
    edited 06/25/24

    @Paul Newcome

    So something like this

    =IF([Run Date]@row = [Run End Date]@row, (TIME([BOT End Time]@row) - TIME([BOT Start Time]@row) * 1440 / 60, (24 - VALUE(LEFT([BOT Start Time]@row, FIND(":", [BOT End Time]@row), -1) + ([BOT End Time]@row) + (([Run End Date]@row - [Run Date]@row - 1) * 24)))

    when I split the formula up to try to make the separate parts work (I learned that from some of your posts), the new front if section does not compute either.

    =IF([Run Date]@row = [Run End Date]@row, (TIME([BOT End Time]@row) - TIME([BOT Start Time]@row) * 1440 / 60)

    =IF([Run Date]@row = [Run End Date]@row, =(TIME([BOT End Time]@row) - TIME([BOT Start Time]@row) * 1440 / 60)