Date Range within one cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Sorry, forgot that part, here goes:

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

Regards,

Peo Sjoblom
 
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?
 
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
))
 
Back
Top