Forum Discussion
Setting Friday as End of Week
Hello, I have a data set that has a list of trip with a Drop Due Date. This drop due date can be any day of the week, but I want to do a count of have the cutoff to be Friday of each week.
I am using the calculation;
Delivery Week = (Transfers[Drop Due Date] + 6 - WEEKDAY(Transfers[Drop Due Date],1))
This seemed to be working at first, but I noticed that any trip with a drop due date of 8/19/2017 (a Saturday) reverts back to the previous Friday (8/18/2017). I need to have these trips be a part of w/e 8/25/2017.
In other words, I need to have any load with a Drop Due Date of Saturday to Friday be included in the Friday at the end of this 'week'.
Here is a snapshot of some of my data.
| Mode | LoadID | Create | Drop Due Date | Delivery Week |
| TL | 69220219 | 8/16/2017 16:29 | 8/20/2017 9:32 | 8/25/2017 9:32 |
| TL | 69235353 | 8/17/2017 8:41 | 8/20/2017 8:58 | 8/25/2017 8:58 |
| TL | 69286142 | 8/18/2017 9:20 | 8/20/2017 8:58 | 8/25/2017 8:58 |
| TL | 69278984 | 8/18/2017 7:27 | 8/20/2017 8:44 | 8/25/2017 8:44 |
| TL | 69196917 | 8/16/2017 10:24 | 8/20/2017 8:14 | 8/25/2017 8:14 |
| TL | 69191957 | 8/16/2017 9:16 | 8/20/2017 8:00 | 8/25/2017 8:00 |
| TL | 69184502 | 8/16/2017 7:20 | 8/20/2017 7:55 | 8/25/2017 7:55 |
| TL | 69121947 | 8/14/2017 13:50 | 8/20/2017 7:12 | 8/25/2017 7:12 |
| TL | 69178885 | 8/16/2017 1:08 | 8/20/2017 7:04 | 8/25/2017 7:04 |
| TL | 69223283 | 8/16/2017 19:31 | 8/20/2017 5:42 | 8/25/2017 5:42 |
| TL | 69179349 | 8/16/2017 2:49 | 8/20/2017 3:00 | 8/25/2017 3:00 |
| TL | 69188600 | 8/16/2017 8:27 | 8/20/2017 2:15 | 8/25/2017 2:15 |
| TL | 69158294 | 8/15/2017 11:39 | 8/19/2017 23:59 | 8/18/2017 23:59 |
| TL | 69271928 | 8/18/2017 0:07 | 8/19/2017 23:59 | 8/18/2017 23:59 |
| TL | 69273110 | 8/18/2017 1:35 | 8/19/2017 23:59 | 8/18/2017 23:59 |
| TL | 69273114 | 8/18/2017 1:39 | 8/19/2017 23:59 | 8/18/2017 23:59 |
| TL | 69273213 | 8/18/2017 2:15 | 8/19/2017 23:59 | 8/18/2017 23:59 |
| TL | 69273286 | 8/18/2017 2:34 | 8/19/2017 23:59 | 8/18/2017 23:59 |
| TL | 69273623 | 8/18/2017 3:41 | 8/19/2017 23:59 | 8/18/2017 23:59 |
| TL | 69273927 | 8/18/2017 4:05 | 8/19/2017 23:59 | 8/18/2017 23:59 |
Try:
(Transfers[Drop Due Date] + IF(WEEKDAY(Transfers[Drop Due Date],1) = 7, 6, 6 - WEEKDAY(Transfers[Drop Due Date],1)))
This looks at the day of week and determines, if Saturday then add 6 days otherwise use logic that is already in place.
2 Replies
- JayHerrera
Advocate I
Try:
(Transfers[Drop Due Date] + IF(WEEKDAY(Transfers[Drop Due Date],1) = 7, 6, 6 - WEEKDAY(Transfers[Drop Due Date],1)))
This looks at the day of week and determines, if Saturday then add 6 days otherwise use logic that is already in place.Thank you for the quick solution! This worked perfectly!
Jeff
