Formula help needed

[WLMPilot]

I have a worksheet that I enter paycheck data on. The date of each check is
automatically computed. Checks is on the 15th and 30th of each month. After
setting the first pay date as 1/15/09, the remainder of the dates are
computed using the following formula (which works fine):


=IF(DAY(A42)=15,MIN(DATE(YEAR(A42),MONTH(A42),30),DATE(YEAR(A42),MONTH(A42)+1,0)),DATE(YEAR(A42),MONTH(A42)+1,15))

....where A42 = 1/15/09.

The problem I have is if the 15th or 30th falls on a weekend, then the
actual date needs to be backed up to Friday's date. Can someone show me how
to accomplish that?

Thanks,
Les

 

[lolo]

I have a worksheet that I enter paycheck data on.  The date of each check is
automatically computed.  Checks is on the 15th and 30th of each month.  After
setting the first pay date as 1/15/09, the remainder of the dates are
computed using the following formula (which works fine):

=IF(DAY(A42)=15,MIN(DATE(YEAR(A42),MONTH(A42),30),DATE(YEAR(A42),MONTH(A42)­+1,0)),DATE(YEAR(A42),MONTH(A42)+1,15))

...where A42 = 1/15/09.

The problem I have is if the 15th or 30th falls on a weekend, then the
actual date needs to be backed up to Friday's date.  Can someone show me how
to accomplish that?

Thanks,
Les

There is a function weekday(date) in excel. It returns the weekday as
an integer from 1 to 7. Try to check if weekdate(yourdate) >= 6 and <=
7 and you should detect a weekend.

Regards
lolo

 

[Bob Phillips]

=IF(DAY(A42)=15,WORKDAY(MIN(DATE(YEAR(A42),MONTH(A42),30),DATE(YEAR(A42),MONTH(A42)+1,0))+1,-1),DATE(YEAR(A42),MONTH(A42)+1,15))

this uses the WORKDAY function from the Analysis Toolpak (ATP). If you get
an error, make sure it is installed (Tools>Addins)