Date Formula

C

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
 
A

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
 
A

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
 
S

Simon Lloyd

Assuming V5 is your date, then use:
=V5+(WEEKDAY(V5)>2)*42-WEEKDAY(V5)+2
this assumes that you have the analasys toolpak add in checked in
add-ins

Clare;472199 said:
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
Click to expand...


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
 
A

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.
 
A

Atishoo

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

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.
 
Top