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

[Soldy_ALDC]

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]

What is the max length of the vacation in days?

[Soldy_ALDC]

15 DAYS

[Soldy_ALDC]

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]

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

[Soldy_ALDC]

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

[Soldy_ALDC]

WELL IT VARIES. SOME GUYS ARE 5 DAYS OTHERS

ARE 10 AND MAX 15

[Aleksi_Alkio]

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]

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_conte... Virus-free. www.avg.com http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_conte... <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

[Aleksi_Alkio]

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

[Soldy_ALDC]

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_conte... Virus-free. www.avg.com http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_conte... <#m_7773119961896785995_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

[Aleksi_Alkio]

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]

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_conte... Virus-free. www.avg.com http://www.avg.com/email-signature?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_conte... <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

[pravse]

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

[Jonathan_Wazar]

Know its an old post, but if it may be useful to anyone else - I think Praveens posted solution is missing a trailing “)”.

…just add an extra “)” at the end after copy paste for your purposes.

Regards

[Jonathan_Wazar]

BTW: Again - Only because I searched and searched for a simple example solution and could not find, I hope this might be helpful to new starts. Used Praveens take on it as base but changed it up a bit so i could understand what was happening. I break things down into smaller simpler parts. Would love a more efficient way, pointers welcome.

UPDATE (1/11/2020): corrected minor issue with calculation, adjusted to span 12 months with possible overlap BUT just one year max. Hope that makes sense. This really does seem SUPER inefficient - there has to be a better way to do this simple calc.

How to calculate difference between dates excluding weekends:

IFS(
AND(
ISNOTBLANK([START DATE]),
ISNOTBLANK([END DATE]),
(HOUR([END DATE]-[START DATE])/24)>7
),

IF(YEAR(DATE([END DATE]))>YEAR(DATE([START DATE])),

IF(((WEEKNUM(DATE([END DATE]))+52) - WEEKNUM(DATE([START DATE])))<2, 0, ((WEEKNUM(DATE([END DATE])) +52)- WEEKNUM(DATE([START DATE]))-1)*5)

, IF((WEEKNUM(DATE([END DATE])) - WEEKNUM(DATE([START DATE])))<2, 0, (WEEKNUM(DATE([END DATE])) - WEEKNUM(DATE([START DATE]))-1)*5))+

IF(
IN(
WEEKDAY([START DATE]),
LIST(2,3,4,5,6)
),
SWITCH(WEEKDAY([START DATE]),2,5,3,4,4,3,5,2,6,1,0),
0+

IF(
IN(
WEEKDAY([END DATE]),
LIST(2,3,4,5,6)
),
WEEKDAY([END DATE]) -1,
0
),
(HOUR([END DATE]-[START DATE])/24)<15,
IF(
WEEKDAY([END DATE]) > WEEKDAY([START DATE]),
(HOUR([END DATE]-[START DATE])/24)+1,
SWITCH(WEEKDAY([END DATE]) -WEEKDAY([START DATE]),-4,2,-3,3,-2,4,-1,5,0,6,1)
)
)

[Aleksi]

Little bit old post… I updated this as we have nowadays the EOWEEK() expression.

WEEKDAY(EOWEEK([Start])) - WEEKDAY([Start]) - IFS(WEEKDAY([Start])<>7,1) +
WEEKDAY([End]) - 1 - IFS(WEEKDAY([End])=7,1) +
((HOUR(EOWEEK([End]) - EOWEEK([Start]))/24)/7) * 5 - 5