Date Range within one cell

I have a date in cell A1 = 05/18/04

I need a formula for cell A2 to show a date range of A1+13days to A1+44days
(end result in A2 should be something like: 5/31/04 - 7/01/04

Then, in cell A3 I have another date that represents when an item was
received. I need a formula in cell A4 that will identify with a "0" or a "1"
if the date the item was received (cell A3) is within the date range in cell
A2.

One way (leave A2 alone in the A4 formula since it refers to a1 you can use
A1 since A2 will not be numerical dates)

=IF(AND(A3>=A1+13,A3<=A1+44),0,1)

assuming you want 0 in within date range and 1 if outside, reverse the order
if not

Regards,

Peo Sjoblom

"Cachod1" <[email protected]> wrote in message
news:[email protected]...
> I have a date in cell A1 = 05/18/04
>
> I need a formula for cell A2 to show a date range of A1+13days to
A1+44days
> (end result in A2 should be something like: 5/31/04 - 7/01/04
>
> Then, in cell A3 I have another date that represents when an item was
> received. I need a formula in cell A4 that will identify with a "0" or a
"1"
> if the date the item was received (cell A3) is within the date range in
cell
> A2.
>
>

Thank you. This formula will allow me to calculate compliance. However, I
still need a formula to show the date range in cell A2. Any ideas?

Thanks

"Peo Sjoblom" wrote:

> One way (leave A2 alone in the A4 formula since it refers to a1 you can use
> A1 since A2 will not be numerical dates)
>
> =IF(AND(A3>=A1+13,A3<=A1+44),0,1)
>
> assuming you want 0 in within date range and 1 if outside, reverse the order
> if not
>
> Regards,
>
> Peo Sjoblom
>
> "Cachod1" <[email protected]> wrote in message
> news:[email protected]...
> > I have a date in cell A1 = 05/18/04
> >
> > I need a formula for cell A2 to show a date range of A1+13days to
> A1+44days
> > (end result in A2 should be something like: 5/31/04 - 7/01/04
> >
> > Then, in cell A3 I have another date that represents when an item was
> > received. I need a formula in cell A4 that will identify with a "0" or a
> "1"
> > if the date the item was received (cell A3) is within the date range in
> cell
> > A2.
> >
> >
>
>
>

Sorry, forgot that part, here goes:

=TEXT(A1+13,"mm/dd/yy")&" - "&TEXT(A1+44,"mm/dd/yy")

Regards,

Peo Sjoblom

"Cachod1" <[email protected]> wrote in message
news:[email protected]...
> Thank you. This formula will allow me to calculate compliance. However,
I
> still need a formula to show the date range in cell A2. Any ideas?
>
> Thanks
>
> "Peo Sjoblom" wrote:
>
> > One way (leave A2 alone in the A4 formula since it refers to a1 you can
use
> > A1 since A2 will not be numerical dates)
> >
> > =IF(AND(A3>=A1+13,A3<=A1+44),0,1)
> >
> > assuming you want 0 in within date range and 1 if outside, reverse the
order
> > if not
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "Cachod1" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a date in cell A1 = 05/18/04
> > >
> > > I need a formula for cell A2 to show a date range of A1+13days to
> > A1+44days
> > > (end result in A2 should be something like: 5/31/04 - 7/01/04
> > >
> > > Then, in cell A3 I have another date that represents when an item was
> > > received. I need a formula in cell A4 that will identify with a "0"
or a
> > "1"
> > > if the date the item was received (cell A3) is within the date range
in
> > cell
> > > A2.
> > >
> > >
> >
> >
> >

Thank You!

I have discovered a new issue as I was working in the formulas you gave me:

I need to somehow add to the formula =IF(AND(A3>=A1+13,A3<=A1+44),0,1)
a way to include in the 0,1 count:

a) if A3 is blank, and today's date is > A1+44, then should be counted as a
1 (orif A3 is blank, and A1+44<today's date, then should be counted as a 1

Can this be done?

"Peo Sjoblom" wrote:

> Sorry, forgot that part, here goes:
>
> =TEXT(A1+13,"mm/dd/yy")&" - "&TEXT(A1+44,"mm/dd/yy")
>
> Regards,
>
> Peo Sjoblom
>
> "Cachod1" <[email protected]> wrote in message
> news:[email protected]...
> > Thank you. This formula will allow me to calculate compliance. However,
> I
> > still need a formula to show the date range in cell A2. Any ideas?
> >
> > Thanks
> >
> > "Peo Sjoblom" wrote:
> >
> > > One way (leave A2 alone in the A4 formula since it refers to a1 you can
> use
> > > A1 since A2 will not be numerical dates)
> > >
> > > =IF(AND(A3>=A1+13,A3<=A1+44),0,1)
> > >
> > > assuming you want 0 in within date range and 1 if outside, reverse the
> order
> > > if not
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > "Cachod1" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have a date in cell A1 = 05/18/04
> > > >
> > > > I need a formula for cell A2 to show a date range of A1+13days to
> > > A1+44days
> > > > (end result in A2 should be something like: 5/31/04 - 7/01/04
> > > >
> > > > Then, in cell A3 I have another date that represents when an item was
> > > > received. I need a formula in cell A4 that will identify with a "0"
> or a
> > > "1"
> > > > if the date the item was received (cell A3) is within the date range
> in
> > > cell
> > > > A2.
> > > >
> > > >
> > >
> > >
> > >
>
>
>

You'll need to do a nested If statement to add the new condition. The
formula below should work.

=IF(AND(ISBLANK(A3)=TRUE,NOW()>(A3+44)),1,IF(AND(C2>(A3+13),A3<=(A3+44)),1,0
))


"Cachod1" <[email protected]> wrote in message
news:[email protected]...
> Thank You!
>
> I have discovered a new issue as I was working in the formulas you gave
me:
>
> I need to somehow add to the formula =IF(AND(A3>=A1+13,A3<=A1+44),0,1)
> a way to include in the 0,1 count:
>
> a) if A3 is blank, and today's date is > A1+44, then should be counted as
a
> 1 (orif A3 is blank, and A1+44<today's date, then should be counted as a
1
>
> Can this be done?
>
> "Peo Sjoblom" wrote:
>
> > Sorry, forgot that part, here goes:
> >
> > =TEXT(A1+13,"mm/dd/yy")&" - "&TEXT(A1+44,"mm/dd/yy")
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "Cachod1" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thank you. This formula will allow me to calculate compliance.
However,
> > I
> > > still need a formula to show the date range in cell A2. Any ideas?
> > >
> > > Thanks
> > >
> > > "Peo Sjoblom" wrote:
> > >
> > > > One way (leave A2 alone in the A4 formula since it refers to a1 you
can
> > use
> > > > A1 since A2 will not be numerical dates)
> > > >
> > > > =IF(AND(A3>=A1+13,A3<=A1+44),0,1)
> > > >
> > > > assuming you want 0 in within date range and 1 if outside, reverse
the
> > order
> > > > if not
> > > >
> > > > Regards,
> > > >
> > > > Peo Sjoblom
> > > >
> > > > "Cachod1" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I have a date in cell A1 = 05/18/04
> > > > >
> > > > > I need a formula for cell A2 to show a date range of A1+13days to
> > > > A1+44days
> > > > > (end result in A2 should be something like: 5/31/04 - 7/01/04
> > > > >
> > > > > Then, in cell A3 I have another date that represents when an item
was
> > > > > received. I need a formula in cell A4 that will identify with a
"0"
> > or a
> > > > "1"
> > > > > if the date the item was received (cell A3) is within the date
range
> > in
> > > > cell
> > > > > A2.
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >
>