If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help needed with a formula for dates in excel 2007
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 |
#2
|
|||
|
|||
Help needed with a formula for dates in excel 2007
=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)15),10+(DAY(A1) 16)*15)
"dloc" wrote: 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 |
#3
|
|||
|
|||
Help needed with a formula for dates in excel 2007
Perfect, thank you!
"Teethless mama" wrote: =DATE(YEAR(A1),MONTH(A1)+(DAY(A1)15),10+(DAY(A1) 16)*15) "dloc" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|