Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update cell based on date range | Excel Discussion (Misc queries) | |||
HOW TO USE A FORMULA TO CHANGE CELL COLOR ACCORDING TO DATE RANGE. | Excel Worksheet Functions | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions | |||
Returning a Value to a Cell Based on a Range of Uncertain Size | Excel Worksheet Functions | |||
Excel: How to return count for each cell within date range criter. | Excel Worksheet Functions |