Formulas for Calculating Time
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
-
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!
-
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?
-
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!
-
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?
-
@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)
-
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
-
=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
-
@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.
-
=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
-
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.
-
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)
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
