I have 2 dates and need to find out if a student missed a full week M-F (not 5 days) of school.
The answer would be a Yes or No
Example: 3/14/17 - 3/23/17 would be NO
Example 3/13/17 - 3/18/17 would be Yes
There are Week of year functions that may or may not help. Can you expand on how you store the data to be searched for such a range?
I have an Exit Field and a Entry Field.
The student Exited on _______ date and reentered on ___________ date. Was this student gone for a full school week?
Let ( [
startDate = [your start date field];
endDate = [your end date field];
startDayOfWeek = dayOfWeek(startDate);
daysUntilMonday = 9-startDayOfWeek;
daysUntilMonday = If ( daysUntilMonday = 7 ; 0 ; daysUntilMonday );
mondayAfterStartDate = startDate + daysUntilMonday;
fridayAfterMonday = mondayAfterStartDate + 4
If ( fridayAfterMonday ≤ endDate ; "Yes" ; "NO" )
If (DayofWeek (beginning_date)=2 and end_date-beginning_date >4;yes;no) should give you what you want.
Edit: this will not work for a student that misses a part of a week and then a full week after the part.
In your example you suggested that both start and end date were days off, but "entry" suggests the last date is the first day back. My calculation is based on the assumption that both start and end dates are days off.
I believe this calc will work from the first day of absence to the return date.
DoW= DayOfWeek ( start );
If ((DoW=2 and days ≥ 7) or (DoW + days >15); "yes";"no"))
rgordon wrote: I believe this calc will work from the first day of absence to the return date. Let([start=date1;Reenter=date2;DoW= DayOfWeek ( start );days= Reenter-start];If ((DoW=2 and days ≥ 7) or (DoW + days >15); "yes";"no"))
No, this doesn't even work for the original example, 3/13/17 - 3/18/17
There are plenty of ranges that include entire M-F sequences that don't start on Mondays and aren't over 15 days long [[edit: I misread the calc, so the preceeding statement is not exactly correct about the "15 days", however it is true that the calc doesn't evaluate correctly according to the OP's example]].
Did you see my answer?
If the end date is "first day back" rather than "last day off" as I had assumed, then my calculation can easily be modified, either by deducting a day from the endDate or by changing the ≤ to a <
Nope. A student would not return on a Saturday (3-18-2017). They would return on a Monday (3-20-2017). Students go to school Monday thru Friday and the OP define a full week as Monday thru Friday.
The OP's own example used Saturday as the end of range and wanted that to evaluate as "Yes".
The OP needs to clarify what the second date actually is (first day back, last day off, or something weird like "day after last day off")
If I alway gave a client what they asked for versus what they actually needed and what actually works, I wouldn't be a very good developer. In the future before telling somebody that they are wrong, maybe a better approach is to ask a question about something that doesn't make sense to you. The OP defined a full week as M-F so it is obvious that a student would not return on a Saturday or Sunday. The OP also clarified in their second post what the second date is
"The student Exited on _______ date and reentered on ___________ date." It is a reenter date.
Sorry I wasn't more clear. The student would get credit for being at school on the return date but not for the exit date.
Exit on Monday (Absent)
Enter Date Friday (Attended)
Did not have a full week of absences M-F
Exit on Thursday March 16
Entered on Friday March 24
Still did not have a full week of M-F because came back on Friday March 24th
The calc I posted was based on your example. I also assumed that a student could not return on a Saturday or Sunday.
Sent from my iPhone
Retrieving data ...