I need help, i have a vacation request form w...

(Soldy ALDC) #1

I need help, i have a vacation request form with start date field and end date field.

simple math [end date] - [start date] = [days off]. but how do I check that my [days off] is not counting the weekend? for example if [start date] is 1/18/2018 and [end date] is 1/22/2018 gives me 4 [days off] but it should be on 2[days off].

(Aleksi Alkio) #2

What is the max length of the vacation in days?

(Soldy ALDC) #3

15 DAYS

(Soldy ALDC) #4

well here is what i came up with based on Praveen’s idea.

=IFS(WEEKNUM([START DATE]) = WEEKNUM([END DATE]),

HOUR([END DATE] - [START DATE])/24 ,

WEEKNUM([END DATE])=WEEKNUM([START DATE])+1,HOUR([END DATE] - [START DATE])/24-2, WEEKNUM([END DATE])=WEEKNUM([START DATE])+2,HOUR([END DATE] - [START DATE])/24-4)

this works for me thanks guys…

(Aleksi Alkio) #5

Have you thought about the situation when the year is changing?

(Soldy ALDC) #6

Yeah i kinda missed that, i think another IF like the weeknum if, should do the job. i’ll keep you posted.

(Soldy ALDC) #7

WELL IT VARIES. SOME GUYS ARE 5 DAYS OTHERS

ARE 10 AND MAX 15

(Aleksi Alkio) #8

Quick solution… You need to arrange 15 different IFS statements to check if the WEEKDAY is from 2 to 6 and if the validation is true, count as one.

(Soldy ALDC) #9

i not sure I understand what you’re telling me… they input [start date] and [end date] so it’s not always going to be 15, they are allowed to request even 1 day vacation. do we have any kind of LOOPS in appsheet? such as FOR…NEXT…LOOP or DO…WHILE…LOOP?

http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail Virus-free. www.avg.com http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

(Aleksi Alkio) #10

I was thinking the same but unfortunately Appsheet doesn’t have either of them.

(Soldy ALDC) #11

Can we make a loop using IFS? we know that [end date] - [start date] = [days off] [variable] = [days off] [days off] = IF(AND([variable]<>0 , OR(WEEKDAY(start date) <> “SATURDAY” , WEEKDAY(start date) <> “SUNDAY”) , [days off] = SUM([days off] +1, ELSE ANOTHER IF

Something like this… i guess

http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail Virus-free. www.avg.com http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail <#m_7773119961896785995_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

(Aleksi Alkio) #12

I was thinking the same with IFS. Like… IFS(IN(WEEKDAY([CheckDate#1]),{2,3,4,5,6}),1)+ IFS(IN(WEEKDAY([CheckDate#2]),{2,3,4,5,6}),1)+ etc.

(Soldy ALDC) #13

ill try what your suggesting, ill let you know if it works.

-than ks

http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail Virus-free. www.avg.com http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

(Praveen Seshadri (AppSheet)) #14

loops and procedural programming are against our principles!

How about something like this:

(([EndDate] - [StartDate])/7) * 5 +

IFS(WEEKDAY([EndDate]) > 5, 5, WEEKDAY([EndDate])) +

IFS((5 - WEEKDAY([StartDate]) >= 0, WEEKDAY[StartDate], 0)

I may have got the math a bit wrong. But the idea is to break the interval down into the initial partial week, some number of whole weeks and then the final partial week