Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Cachod1
 
Posts: n/a
Default 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   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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   Report Post  
Cachod1
 
Posts: n/a
Default

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   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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   Report Post  
Cachod1
 
Posts: n/a
Default

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   Report Post  
Jeff Stevens
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Update cell based on date range deversole Excel Discussion (Misc queries) 3 July 6th 05 01:58 PM
HOW TO USE A FORMULA TO CHANGE CELL COLOR ACCORDING TO DATE RANGE. terry Excel Worksheet Functions 2 December 27th 04 05:07 AM
Using formulas to determine date in one cell based on date in anot Gary Excel Worksheet Functions 2 November 22nd 04 09:11 AM
Returning a Value to a Cell Based on a Range of Uncertain Size amc422 Excel Worksheet Functions 7 November 14th 04 04:03 PM
Excel: How to return count for each cell within date range criter. Louisa Excel Worksheet Functions 0 November 5th 04 12:58 PM


All times are GMT +1. The time now is 08:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"