biweekly formatting

  • Thread starter Thread starter Guest
  • Start date Start date
I don't any other replies yet so try this
If you have the date in A1
=IF(ISEVEN(A1-WEEKDAY(A1,1)+6),A1-WEEKDAY(A1,1)+6,A1-
WEEKDAY(A1,1)+6-7)
will return a biweekly Friday
=A1=B1 in C1 returns true if A1 is a bi-weekly friday
Conditional format A1 based on c1 = "true"

To change the day, alter the +6 to +1 for Sunday through
+7 for Saturday
If you get the wrong week, change Iseven to Isodd

I hope to see a more elegant solution posted.
Because of circular logic I can't this formula into
conditional formatting itself.

Good Luck
 
P.S. You need the Analysis ToolPak under Tools/Add-Ins..
for the IsEven and IsOdd functions
 
Here's a much simpler way

If your date is in A1
In conditional formatting using the "Formula is" option type
=mod(a1,14)=0
This will format your cell every other Saturday
In the place of 0 use
Saturday 0 or 7
Sunday 1 or 8
to
Friday 6 or 13
Depending on the day and week you want
 
Back
Top