Fomula only half working

[AlanStotty]

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.

 

[AlanStotty]

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

 

[Guest]

Try:

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

 

[Guest]

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

 

[Guest]

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

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

 

[AlanStotty]

Try:

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














- 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.

 

[Guest]

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]

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

 

[Guest]

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

(remove NOSPAM)