Fomula only half working
 

Hi,

I am using this formula:
AND(T$4=DATE(YEAR($AR5),MONTH($AR5),1),T
$4<=DATE(YEAR($AS5),MONTH($AS5)+1,0))

(the logic is that first cell is fist half of month and the other
second half):

01/03/2007(cell: T4) 16/03/2007(Cell: T5)

They are both returning true with

AR5 = 16/03/2007
AS5 = 30/03/2007

What I would like is to have cell T4 to return false and T5 to be
true. This is because AR5 is above the 15th of March.

My sheet has the 12 months split this way, the above is an example.

Any ideas?

Thanks in advance.

Fomula only half working
 

I put in something similar before, hopefully this time is clear.
Thanks.

Fomula only half working
 

Try:

=AND(T$4=AR5,T$4<=DATE(YEAR($AS5),MONTH($AS5)+1,0 ))

"AlanStotty" wrote:

Hi,

I am using this formula:
AND(T$4=DATE(YEAR($AR5),MONTH($AR5),1),T
$4<=DATE(YEAR($AS5),MONTH($AS5)+1,0))

(the logic is that first cell is fist half of month and the other
second half):

01/03/2007(cell: T4) 16/03/2007(Cell: T5)

They are both returning true with

AR5 = 16/03/2007
AS5 = 30/03/2007

What I would like is to have cell T4 to return false and T5 to be
true. This is because AR5 is above the 15th of March.

My sheet has the 12 months split this way, the above is an example.

Any ideas?

Thanks in advance.

Fomula only half working
 

the first thin is I would like you to fix this one stattement becaue there is
not such thin as a Zero day in the month. I changged the <= to <. this has
nothing to do with the problem is just annoys me. sorry!

$4<DATE(YEAR($AS5),MONTH($AS5)+1,1)

I think your should be using the Day() functtion which returns the day of
the month between 1-31

So you should be comparing
=Day(AR5)
=Day(AS5)
=Day(T4)
=Day(T5)

I'm not sure what you are trying to do. but using day will help. If you
have problems try breating a truth table like this:

Day(T4) < 15 Day(T4) = 15
Day(T5) < 15 True False
Day(T5) = 15 False True

"AlanStotty" wrote:

Hi,

I am using this formula:
AND(T$4=DATE(YEAR($AR5),MONTH($AR5),1),T
$4<=DATE(YEAR($AS5),MONTH($AS5)+1,0))

(the logic is that first cell is fist half of month and the other
second half):

01/03/2007(cell: T4) 16/03/2007(Cell: T5)

They are both returning true with

AR5 = 16/03/2007
AS5 = 30/03/2007

What I would like is to have cell T4 to return false and T5 to be
true. This is because AR5 is above the 15th of March.

My sheet has the 12 months split this way, the above is an example.

Any ideas?

Thanks in advance.

Fomula only half working
 

Joel,
A Day of 0 returns the last day of the previous month

e.g =Date(07,03,0) will return 28/02/07.

"Joel" wrote:

the first thin is I would like you to fix this one stattement becaue there is
not such thin as a Zero day in the month. I changged the <= to <. this has
nothing to do with the problem is just annoys me. sorry!

$4<DATE(YEAR($AS5),MONTH($AS5)+1,1)

I think your should be using the Day() functtion which returns the day of
the month between 1-31

So you should be comparing
=Day(AR5)
=Day(AS5)
=Day(T4)
=Day(T5)

I'm not sure what you are trying to do. but using day will help. If you
have problems try breating a truth table like this:

Day(T4) < 15 Day(T4) = 15
Day(T5) < 15 True False
Day(T5) = 15 False True

"AlanStotty" wrote:

Hi,

I am using this formula:
AND(T$4=DATE(YEAR($AR5),MONTH($AR5),1),T
$4<=DATE(YEAR($AS5),MONTH($AS5)+1,0))

(the logic is that first cell is fist half of month and the other
second half):

01/03/2007(cell: T4) 16/03/2007(Cell: T5)

They are both returning true with

AR5 = 16/03/2007
AS5 = 30/03/2007

What I would like is to have cell T4 to return false and T5 to be
true. This is because AR5 is above the 15th of March.

My sheet has the 12 months split this way, the above is an example.

Any ideas?

Thanks in advance.

Fomula only half working
 

On Mar 15, 10:44 am, Toppers
wrote:
Try:

=AND(T$4=AR5,T$4<=DATE(YEAR($AS5),MONTH($AS5)+1,0 ))



"AlanStotty" wrote:
Hi,

I am using this formula:
AND(T$4=DATE(YEAR($AR5),MONTH($AR5),1),T
$4<=DATE(YEAR($AS5),MONTH($AS5)+1,0))

(the logic is that first cell is fist half of month and the other
second half):

01/03/2007(cell: T4) 16/03/2007(Cell: T5)

They are both returning true with

AR5 = 16/03/2007
AS5 = 30/03/2007

What I would like is to have cell T4 to return false and T5 to be
true. This is because AR5 is above the 15th of March.

My sheet has the 12 months split this way, the above is an example.

Any ideas?

Thanks in advance.- Hide quoted text -

- Show quoted text -

AR5 = 16/03/2007
AS5 = 30/03/2007


Thanks, that works for the start (AR5) but not for end (AS5). i.e. if
AS5 = 14/03/07, then both will still show true, whereas I want only
the first cell to show true and the second false.

Fomula only half working
 

I confess to being confused about what your are trying to do: it appears you
want to test if a date is in the first or second half of a month?

So what are T4 and T5 representing?

AND(T$4=DATE(YEAR($AR5),MONTH($AR5),1),T$4<=DATE( YEAR($AS5),MONTH($AS5),15,0)) would test for first half?


AND(T$4=DATE(YEAR($AR5),MONTH($AR5),16),T$4<=DATE (YEAR($AS5),MONTH($AS5)+1,,0)) would test for second half?



"AlanStotty" wrote:

On Mar 15, 10:44 am, Toppers
wrote:
Try:

=AND(T$4=AR5,T$4<=DATE(YEAR($AS5),MONTH($AS5)+1,0 ))



"AlanStotty" wrote:
Hi,

I am using this formula:
AND(T$4=DATE(YEAR($AR5),MONTH($AR5),1),T
$4<=DATE(YEAR($AS5),MONTH($AS5)+1,0))

(the logic is that first cell is fist half of month and the other
second half):

01/03/2007(cell: T4) 16/03/2007(Cell: T5)

They are both returning true with

AR5 = 16/03/2007
AS5 = 30/03/2007

What I would like is to have cell T4 to return false and T5 to be
true. This is because AR5 is above the 15th of March.

My sheet has the 12 months split this way, the above is an example.

Any ideas?

Thanks in advance.- Hide quoted text -

- Show quoted text -

AR5 = 16/03/2007
AS5 = 30/03/2007


Thanks, that works for the start (AR5) but not for end (AS5). i.e. if
AS5 = 14/03/07, then both will still show true, whereas I want only
the first cell to show true and the second false.

Fomula only half working
 

Is there any way I can post my excel sheet to show you?

Fomula only half working
 

Post (with clear explanation!) to toppers at nospam.johntopley.fsnet.co.uk

(remove NOSPAM)

"AlanStotty" wrote:

Is there any way I can post my excel sheet to show you?