Setting Friday as End of Week

[jhenscheid]

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

[JayHerrera · Accepted solution]

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. 

[jhenscheid]

Thank you for the quick solution!  This worked perfectly!

Jeff