Help needed with a formula for dates in excel 2007

D

dloc

I need help with a formula for dates in excel 2007

If Pay Date is from the 1st to the 15th of the month then Remit Date would
be the 25th of the same month.
If Pay Date is after the 15th, the Remit Date would be the 10th of the
following month.

a1= Pay Date b1=Remit Date
01-04-10 01-25-10
01-18-10 02-10-10
02-15-10 02-25-10
02-26-10 03-10-10, etc

This is what I have so far, it works for Jan, but not Feb or Mar.

=IF(Wages_2010[[#This Row],[Pay
Date]]<=DATE(2010,1,15),DATE(2010,1,25),DATE(2010,MONTH(1)+1,10))

I need something that will work for the entire year, without having to
change the formula every month.

Thanks
 
D

dloc

Perfect, thank you!

Teethless mama said:
=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)>15),10+(DAY(A1)<16)*15)


dloc said:
I need help with a formula for dates in excel 2007

If Pay Date is from the 1st to the 15th of the month then Remit Date would
be the 25th of the same month.
If Pay Date is after the 15th, the Remit Date would be the 10th of the
following month.

a1= Pay Date b1=Remit Date
01-04-10 01-25-10
01-18-10 02-10-10
02-15-10 02-25-10
02-26-10 03-10-10, etc

This is what I have so far, it works for Jan, but not Feb or Mar.

=IF(Wages_2010[[#This Row],[Pay
Date]]<=DATE(2010,1,15),DATE(2010,1,25),DATE(2010,MONTH(1)+1,10))

I need something that will work for the entire year, without having to
change the formula every month.

Thanks
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Help with dates 2
Formulas with dates 3
Lookup and count all cells that fall between 2 dates 3
Date Function 2
Formula help needed 2
lookup help. lookup result based on data in 2 columns 3
Conditional formula 7
Sum value between two dates and copy to new cell 6

Top