Formulas for Calculating Time

Romano el Polako

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

https://community.smartsheet.com/discussion/comment/416924#Comment_416924

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!

Romano el Polako

Hey @richard_abra

Thanks, I will try and let you know!

richard_abra

https://community.smartsheet.com/discussion/comment/418296#Comment_418296

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 😂

Romano el Polako

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

richard_abra

https://community.smartsheet.com/discussion/comment/418359#Comment_418359

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!

richard_abra

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

@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)

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

@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

@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.

allison.worley

@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

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.

allison.worley

@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)