How to count weekend days between two dates upon user's input

[Boyoung]

hi friends.

I want to know how many weekend days between two dates when user input two dates.

I want to calculate in app formula. 

Please let me help and give me your tips.

thanks for  your help.

[Suvrutt_Gurjar · Accepted solution]

Please take a look at the post below.

Solved: Workdays between 2 dates - Page 2 - Google Cloud Community

The discussion in the post is about workdays between two days. You can create an expression for the holidays based on

"Total Days between two dates- Workdays between two dates" .

In general, it is better to evaluate if you can use the spreadsheet's NETWORKDAYS() function ( as recommended by Marc Dillon in the post referred) because AppSheet expressions can become unwieldly for this requirement. Again by subtracting NETWORKDAYS() from total days between two dates, you could get weekend days.

[Suvrutt_Gurjar · Accepted solution]

Please take a look at the post below.

Solved: Workdays between 2 dates - Page 2 - Google Cloud Community

The discussion in the post is about workdays between two days. You can create an expression for the holidays based on

"Total Days between two dates- Workdays between two dates" .

In general, it is better to evaluate if you can use the spreadsheet's NETWORKDAYS() function ( as recommended by Marc Dillon in the post referred) because AppSheet expressions can become unwieldly for this requirement. Again by subtracting NETWORKDAYS() from total days between two dates, you could get weekend days.

[Boyoung]

MY App is vacation request in our company. I want to check the input data before insert into spreadsheet. If total remain vacation count is less than user's request vacation days, I want to block.

[Suvrutt_Gurjar · Accepted solution]

Okay. Got it. Then please evaluate the AppSheet functions based expressions in the referred post and try as suggested.

---

@Suvrutt_Gurjar wrote:

The discussion in the post is about workdays between two days. You can create an expression for the holidays based on

"Total Days between two dates- Workdays between two dates" .

---

[Suvrutt_Gurjar]

@Boyoung 

For calculating weekend days of Saturday and Sunday, between two dates, please try the expression below. It considers both the [Start Date] and [End Date] inclusive for the holidays calculation. Please do test well for a large number of test cases to ensure it matches your requirement.

HOUR(IF(WEEKDAY([End Date])=7, [End Date],EOWEEK([End Date]-7)) -
              EOWEEK([Start Date])

             )*2/168 +

             1 +

            IF(WEEKDAY([End Date])= 7, 0, 1) +

            IF(WEEKDAY([Start Date])= 1, 1, 0)