12 Replies Latest reply on Mar 23, 2017 12:55 PM by rgordon

# I have 2 dates and need to find out if a student missed a full week M-F (not 5 days) of school.

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!

• ###### 1. Re: I have 2 dates and need to find out if a student missed a full week M-F (not 5 days) of school.

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

• ###### 2. Re: I have 2 dates and need to find out if a student missed a full week M-F (not 5 days) of school.

Thanks Beverly.

Yes.

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?

• ###### 3. Re: I have 2 dates and need to find out if a student missed a full week M-F (not 5 days) of school.

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

)

• ###### 4. Re: I have 2 dates and need to find out if a student missed a full week M-F (not 5 days) of school.

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.

• ###### 5. Re: I have 2 dates and need to find out if a student missed a full week M-F (not 5 days) of school.

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.

• ###### 6. Re: I have 2 dates and need to find out if a student missed a full week M-F (not 5 days) of school.

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

• ###### 7. Re: I have 2 dates and need to find out if a student missed a full week M-F (not 5 days) of school.

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]].

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 <

• ###### 8. Re: I have 2 dates and need to find out if a student missed a full week M-F (not 5 days) of school.

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.

• ###### 9. Re: I have 2 dates and need to find out if a student missed a full week M-F (not 5 days) of school.

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

• ###### 10. Re: I have 2 dates and need to find out if a student missed a full week M-F (not 5 days) of school.

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.

• ###### 11. Re: I have 2 dates and need to find out if a student missed a full week M-F (not 5 days) of school.

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

• ###### 12. Re: I have 2 dates and need to find out if a student missed a full week M-F (not 5 days) of school.

The calc I posted was based on your example. I also assumed that a student could not return on a Saturday or Sunday.

Roy Gordon

PSFE

Sent from my iPhone