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!
-
