Formulas for Calculating Time

Options

• Options

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

• Options

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!

• Options

Thanks, I will try and let you know!

• Options

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 😂

• Options

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

• edited 02/07/24
Options

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!

• Options