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

Any ideas?

Thanks!

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

Any ideas?

Thanks!

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" )

)

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 <

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

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?

beverly