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_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>
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_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>
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_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>
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
User | Count |
---|---|
38 | |
35 | |
27 | |
23 | |
18 |