Rounding weekend days to monday

**ScottLor** · 04-17-2013, 09:11 AM

Hi guys. I am trying to calculate some dates for events that have to occur a certain number of days apart from each other. So, if event 1 is 4/17/2014, I would like to calculate a day 84 days before that date. A1 = 4/17/2014. In B1 =A1-84. But I only want to generate values in B1 that are mondays. The 84 day difference should include all days including weekend days, but the resulting date should round to the nearest monday. Is that possible? Another cell I would like to calculate a difference and the resulting date should only be a weekday. Is that possible if only monday's isn't possible. I am stumped.

**ScottLor** · 04-17-2013, 09:22 AM

I figured out the equation for the calculating results that are only weekdays :

=IF(OR(WEEKDAY(M6-84)=2,WEEKDAY(M6-84)=3,WEEKDAY(M6-84)=4,WEEKDAY(M6-84)=5,WEEKDAY(M6-84)=6),M6-84,IF(WEEKDAY(M6-84)=1,M6-83,M6-85))

Where there is a date in cell M6, however calulating only mondays has me a bit confused.

**benishiryo** · 04-17-2013, 09:28 AM

hi ScottLor, maybe:
=A1-84-WEEKDAY(A1-84,2)+IF(ROUND(WEEKDAY(A1-84,2)/7,0),8,1)

but DDL would probably come up with something better since he's looking at this thread. i'm assuming Thurs should be brought forward to the next Mon too

**daddylonglegs** · 04-17-2013, 09:33 AM

When you say "nearest Monday" do you genuinely mean the nearest (or do you always want the Monday before....or perhaps the Monday after?). To get the nearest (e.g. if A1-84 = Thursday it goes back to the Monday 3 days before, if A1-84 = Friday it goes forward to the next Monday) try this formula

=A1-84+3-WEEKDAY(A1-84+3,3)

**Ghozi Alkatiri** · 04-17-2013, 09:36 AM

write at B1
=A1-84-MOD(WEEKDAY(A1-84,3),7)

**ScottLor** · 04-17-2013, 09:53 AM

Thanks DDL, I did mean the nearest monday (forward from friday and back from thurs). That equation works great. Can you help me understand equation?

Why did you add the +3 term and what is the [return type] term in weekday that you entered 3 for?

**daddylonglegs** · 04-17-2013, 10:54 AM

If you use the formula

=WEEKDAY(D2,3) when D2 contains a date that gives you zero on a Monday through to 6 on a Sunday so you can use this formula to get the Monday on or before D2

=D2-WEEKDAY(D2,3)

In your case we need to use A1-84 in place of D2 so that becomes

=A1-84-WEEKDAY(A1-84,3)

but that would always "round" back to the previous Monday, not the nearest, so if we add 3 days in each part that will give the nearest instead, i.e.

=A1-84+3-WEEKDAY(A1-84+3,3)

You can apply the formula for any number of days so if you want 50 days before (rounded to nearest Monday) just replace the two 84s with 50s