Hello Community, How can I get a duration c...

(VICTOR VERA) #1

Hello Community,

How can I get a duration calculation between 2 dates to ignore or not count weekend days??

(VICTOR VERA) #2

Hi is this something that can be done or s it very complicated to accomplish

(VICTOR VERA) #3

Is this Possible??@Aleksi_Alkio Translate

(Aleksi Alkio) #4

Yes it’s possible but not very simple :slight_smile:

(VICTOR VERA) #5

@Aleksi_Alkio would I have to use many Ifs? Do you have a hint, Im getting better at more advanced expressions :grinning:

(Jacob Lin [AppSheet]) #6

@VICTOR_VERA This is a simplified formula for what you’re asking, if it doesn’t work completely then you’ll have to look up a more complex one that’ll work for all cases. It seems to work for most cases, bar if you have start and end on same day, and some extreme edge cases.

1 + ((HOUR([End] - [Start]) / 24) * 5 - (WEEKDAY([Start]) - WEEKDAY([End])) * 2) / 7 - IF(WEEKDAY([End]) = 7, 1, 0) - IF(WEEKDAY([Start]) = 1, 1, 0)

All I did to create this formula was look up some code on something like Stack Overflow and then translate that code into Appsheet statements.

Make sure to use this in a virtual column.

(VICTOR VERA) #7

Wow that worked like a charm, thank you so much, I now see light at the end of the tunnel +Jacob Lin