Home |
Search |
Today's Posts |
#1
|
|||
|
|||
biweekly formatting
Is there a way to format a date to be a unique format for biweekly dates
|
#2
|
|||
|
|||
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 -----Original Message----- Is there a way to format a date to be a unique format for biweekly dates . |
#3
|
|||
|
|||
P.S. You need the Analysis ToolPak under Tools/Add-Ins..
for the IsEven and IsOdd functions -----Original Message----- 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 -----Original Message----- Is there a way to format a date to be a unique format for biweekly dates . . |
#4
|
|||
|
|||
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 On Wed, 16 Feb 2005 13:04:12 -0800, "BobT" wrote: 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 -----Original Message----- Is there a way to format a date to be a unique format for biweekly dates . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting in Excel Help Please..... | Excel Discussion (Misc queries) | |||
Formatting dates in the future | Excel Worksheet Functions | |||
Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |