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].
What is the max length of the vacation in days?
15 DAYS
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…
Have you thought about the situation when the year is changing?
Yeah i kinda missed that, i think another IF like the weeknum if, should do the job. i’ll keep you posted.
WELL IT VARIES. SOME GUYS ARE 5 DAYS OTHERS
ARE 10 AND MAX 15
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.
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>
I was thinking the same but unfortunately Appsheet doesn’t have either of them.
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>
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.
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>
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
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
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)
)
)
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