Find Specific date in Biwwekly Based on date
 

I am trying to find the next biweekly end date based on todays date.
The biweeklies end on Saturdays and that is the date I need to
return. 1-24-09, 1-10-09, 12-27-08. These are some of the dates.

Is there a way to do this with functions in excel or is this going to
have to be a UDF?

Thanks in advance,
Jay

Find Specific date in Biwwekly Based on date
 

Try this

=IF(MOD(WEEKNUM(A1),2)=1,A1+CHOOSE(WEEKDAY(A1),13, 12,11,10,9,8,7),A1+CHOOSE(WEEKDAY(A1),6,5,4,3,2,1, 0))

Mike

"jlclyde" wrote:

I am trying to find the next biweekly end date based on todays date.
The biweeklies end on Saturdays and that is the date I need to
return. 1-24-09, 1-10-09, 12-27-08. These are some of the dates.

Is there a way to do this with functions in excel or is this going to
have to be a UDF?

Thanks in advance,
Jay

Find Specific date in Biwwekly Based on date
 

Put a date in F2 and in G2 use
=F2+IF(WEEKDAY(F2)=7,14,7)-WEEKDAY(F2)+(MOD(F2+IF(WEEKDAY(F2)=7,14,7)-WEEKDAY(F2)-DATE(2008,12,27),14)<0)*7

Now the first part of this =F2+IF(WEEKDAY(F2)=7,14,7)-WEEKDAY(F2) finds the
date of the next Saturday
The second part
+(MOD(F2+IF(WEEKDAY(F2)=7,14,7)-WEEKDAY(F2)-DATE(2008,12,27),14)<0)*7 adds
7 if the next Saturday date is not an even number of 14 days away from
27/Dec/2008

Test this with various dates in F2; it works for me
Now replace F2 by TODAY()
=TODAY()+IF(WEEKDAY(TODAY())=7,14,7)-WEEKDAY(TODAY())+(MOD(TODAY()+IF(WEEKDAY(TODAY())= 7,14,7)-WEEKDAY(TODAY())-DATE(2008,12,27),14)<0)*7
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"jlclyde" wrote in message
...
I am trying to find the next biweekly end date based on todays date.
The biweeklies end on Saturdays and that is the date I need to
return. 1-24-09, 1-10-09, 12-27-08. These are some of the dates.

Is there a way to do this with functions in excel or is this going to
have to be a UDF?

Thanks in advance,
Jay

Find Specific date in Biwwekly Based on date
 

Both work great. Thank you for the ideas. I always seem to forget
about MOD.
Jay