Prev: Summing a crosstab
Next: How do you search within a document?
From: dloc on 16 Mar 2010 18:55
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

From: Teethless mama on 16 Mar 2010 21:06
=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
>
From: dloc on 17 Mar 2010 11:04
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
> >
 | 
Pages: 1
Prev: Summing a crosstab
Next: How do you search within a document?