How to round a date to one of two different weekdays

1. How to round a date to one of two different weekdays

I have a list with delivery times from a supplier where i have fixed shipment days (Tuesday and Friday) every week.

in my data set i often have all kinds of dates (different week days)

does anyone know a formula where i can round down a certain date to either the closest Tuesday or Friday.

e.g:
- delivery date Wednesday Thursday need to be rounded down to Tuesday (same week)
- Delivery date Monday needs to be rounded down to Friday (the week before)
- If the delivery date is already a Tuesday or a Friday i just need to keep that date.

any help would be highly appreciated.

2. Re: How to round a date to one of two different weekdays

With the date in B2 how about
Formula:
`Please Login or Register  to view this content.`

3. Re: How to round a date to one of two different weekdays

In F2 then copy down.
``Please Login or Register  to view this content.``

4. Re: How to round a date to one of two different weekdays

Thanks Fluff13 that seems to work. but this actually raises an extra question.

i have a list of bankholidays and i would like to avoid that the formula rounds down to a bank holiday. it should round down to either the tuesday or the friday before the holiday.

e.g.

- Monday 5. April would round down to Friday 2. April.
But 2. April is "Easter Friday" and therefore in this case I would like the formula to round down to Tuesday 30. March instead.

5. Re: How to round a date to one of two different weekdays

WORKDAY.INTL has a parameter for holiday list: have you tried adding that?

=WORKDAY.INTL(B2+1,-1,"1011011",holidays) where "Holidays" is a list (named range?) of holiday dates.

6. Re: How to round a date to one of two different weekdays

JohnTopley: thankyou for your input. it seems to have solved my problem.

im trying to figure out the logic of the Workday.intl formula and i get most of it. but im struggeling to figure out why i need to add +1 to the date and the subtract -1 in the next step. B2+1,-1.

can you help me explaining this

7. Re: How to round a date to one of two different weekdays

Originally Posted by MaxPoulsen
but im struggeling to figure out why i need to add +1 to the date and the subtract -1 in the next step. B2+1,-1
Try place together to see the difference:
(1) =WORKDAY.INTL(B2,-1,"1011011",\$R\$1:\$R\$4)
and
(2) =WORKDAY.INTL(B2+1,-1,"1011011",\$R\$1:\$R\$4)

If B2 fell into tue or fri or holidays, i.e, B2="8-Jan-2021" (Fri)
(1) came back to prv tue 5-Jan
(2) stay in same day (Fri).
+1 then -1, to avoid this.

8. Re: How to round a date to one of two different weekdays

arr i see, thanks for clarifying.

9. Re: How to round a date to one of two different weekdays

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

There are currently 1 users browsing this thread. (0 members and 1 guests)