Help needed with a formula for dates in excel 2007

  • Thread starter Thread starter dloc
  • Start date Start date
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
 
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...
 
Back
Top