| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help needed with creating a Formula in Excel 2007 please ? | Excel Worksheet Functions | |||
| Formula needed to calculate difference between Two dates and times | New Users to Excel | |||
| Formula Needed to Compare Dates and return a running total | Excel Worksheet Functions | |||
| FORMULA NEEDED TO COUNT DAYS IN EXCEL BETWEEN DATES | Excel Worksheet Functions | |||
| formula needed to track dates event happened | Excel Worksheet Functions | |||
Linear Mode
