Formula help needed
 

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),MON TH(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

Formula help needed
 

On 20 Sty, 14:36, WLMPilot wrote:
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),MO NTH(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

Formula help needed
 

=IF(DAY(A42)=15,WORKDAY(MIN(DATE(YEAR(A42),MONTH(A 42),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 (ToolsAddins)

--
__________________________________
HTH

Bob

"WLMPilot" wrote in message
...
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),MON TH(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