Combining two formulas

[matthewccna]

Combining two formulas

I’m doing some work with a time sheet, in A1 I have a formula tha
calculates the Friday date of the current week

=((DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1),3)+7*(INT((TODAY()-SUM(MOD(DATE(YEAR(TODAY()-MOD(TODAY()-2,7)+3),1,2)
{1E+99,7})*{1,-1})+5)/7)-1))+4)

in A2 I have an equation that takes this date compares it to a pay dat
which is every two weeks and sets the date for the last Friday in th
pay period by taking a date that is the last Friday in a pay period an
checking that there is an even number of weeks between then and now.

=IF(EVEN(DATEDIF(DATE(2003,11,7),A1,"d")/7)=DATEDIF(DATE(2003,11,7),A1,"d")/7,A1,A1+7)

in A3 I have attempted to combine the two formulas into:

=IF(EVEN(DATEDIF(DATE(2003,11,7)
((DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1),3)+7*(INT((TODAY()-SUM(MOD(DATE(YEAR(TODAY()-MOD(TODAY()-2,7)+3),1,2)
{1E+99,7})*{1,-1})+5)/7)-1))+4),"d")/7)=DATEDIF(DATE(2003,11,7)
((DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1),3)+7*(INT((TODAY()-SUM(MOD(DATE(YEAR(TODAY()-MOD(TODAY()-2,7)+3),1,2)
{1E+99,7})*{1,-1})+5)/7)-1))+4),"d")/7
((DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1),3)+7*(INT((TODAY()-SUM(MOD(DATE(YEAR(TODAY()-MOD(TODAY()-2,7)+3),1,2)
{1E+99,7})*{1,-1})+5)/7)-1))+4)
((DATE(YEAR(TODAY()),1,1)-WEEKDAY(DATE(YEAR(TODAY()),1,1),3)+7*(INT((TODAY()-SUM(MOD(DATE(YEAR(TODAY()-MOD(TODAY()-2,7)+3),1,2)
{1E+99,7})*{1,-1})+5)/7)-1))+4)+7)

when I hit enter I receive a message box that says: “The formula yo
have typed contains an error” if I select OK the fourth TODAY() become
highlighted.

Matthe

 

[Vasant Nanavati]

You are using a cannon to kill a fly:

For the first formula, use:

=TODAY()+5-WEEKDAY(TODAY(),2)

For the second:

=A1+7*MOD(A1-DATEVALUE("7-Nov-2003"),2)

For a combination of the two:

=TODAY()+5-WEEKDAY(TODAY(),2)+7*MOD(TODAY()+5-WEEKDAY(TODAY(),2)-DATEVALUE("
7-Nov-2003"),2)

No doubt there are even shorter ways.