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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.

Date Range within one cell
 

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(C 2(A3+13),A3<=(A3+44)),1,0
))


"Cachod1" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.