Date Formula

[Clare]

I have a date in column V5 and need to calculate 6 weeks from this date to
prompt a review. I was going to use =(V5+42) but I need to make the date
round up or down to the nearest Monday. Is there any way of doing this.

Thaks

 

[Sean Timmons]

=V5+44-WEEKDAY(V5)

 

[Atishoo]

Hi Clare.
You need to use the weekday formula to calculate the day of the week and
then use the if function to see if it falls in the later or earlier part of
the week. then add or subtract it from six weeks if its the earlier part of
the week to take it back to the last monday or from 7 weeks to take it to the
next monday if its in the later part of the week.
try this :

=IF(WEEKDAY(v5,1)<4,v5+41-(WEEKDAY(v5,1)),v5+48-(WEEKDAY(v5,1)))

hope its Ok
thanks John

 

[Atishoo]

Sorry Clare
Think I got my 7 times tables wrong or something! I think formula should read:
=IF(WEEKDAY(B5,1)<4,B5+43-(WEEKDAY(B5,1)),B5+50-(WEEKDAY(B5,1)))

all the best John

 

[Atishoo]

I of course mean V5 was using B5 in my example:

=IF(WEEKDAY(V5,1)<4,V5+43-(WEEKDAY(V5,1)),B5+50-(WEEKDAY(V5,1)))

This assumes that you want mon tue and wed dates to be booked for the monday
prior and thur fri sat and sun to fall to the mon following. If you wish thur
to fall to the mon prior change the >4 to >5.

 

[Atishoo]

try again without me leaving B5 in: sorry
=IF(WEEKDAY(V5,1)<4,V5+43-(WEEKDAY(V5,1)),V5+50-(WEEKDAY(V5,1)))

 

[Clare]

Thanks Sean that works great
--
Clare

=V5+44-WEEKDAY(V5)

 

[Clare]

Hi Atishoo

Thanks for all your help it worked tried using the other formulas which were
great but yours rounded up and down only thing was I had to change the 1's to
2's to make it round up to Monday's rather than Sundays. Thanks again would
never have worked out myself.

 

[Atishoo]

Welcome, just out of interest which way did you want thursdays to go?