Formula help

[Adam]

Hi All,

I'm trying to create a formula but can't work it out.

We've decided that were going to class each month from the 20th to the
21st, rather than the 1st of the 1st.

So 21/05/05 to 20/06/05 (dd/mm/yy) will all be classed as May, and from
21/06/05 it will be classed as June.

I have a list of dates and would like a formula in the column next to
it that will work out what month were classing the date in and put the
month in an MMM format .i.e May

So
(dd/mm/yy) (mm)
21/05/05 May
22/05/05 May
20/06/05 May
21/06/05 June
30/06/05 June
21/07/05 Aug

I should be able to do this but its suprisingly difficult !

Many Thanks

Adam

 

[Jon Quixley]

Adam,

There is probably a more elegant way of doing this, though it might
also be more complex than this idea

Construct a lookup table of the entire calendar from 1/1/05 through to
the end of December in column A and in the next column (B) set you
months as you want them. You can then use this as a lookup/correction
device for your data.

The formula you'd need would be simply +VlLOOKUP(N1,sheet2!A1:B365,2)
where N1 is the actual (normal date you want to change, A1:B365 is your
table with normal dates in ColA and the modified ones in ColB. The
number two at the end instructs the formula once it has found a match
between N and A to return the second column data on the same row.

Cheers
Jon

 

[Bryan Hessey]

Hi,

format column B to be mmm and put

=IF(DAY(A1)<21,A1,A1+(HLOOKUP(MONTH(A1),
{1,2,3,4,5,6,7,8,9,10,11,12;28,31,30,31,30,31,31,31,30,31,30,31},2,TRUE)))

in B1 (CTRL/Shift/Enter for the array) and copy down to cover th
entries in Column A

 

[David McRitchie]

Hi Adam,
Check your example, it's hard to test validity if your expected results
don't match your definitions. See my comment on your expected
results.

=DATE(YEAR(A1),MONTH(A1)-(DAY(A1)<21),1)
to produce a text month
=TEXT(DATE(YEAR(A1),MONTH(A1)-(DAY(A1)<21),1),"MMMM")
you could use this with same result
=TEXT(DATE(YEAR(A1),MONTH(A1)-(DAY(A1)<21),21),"MMMM")

 

[Bryan Hessey]

I guess another easy option is to format column B to Custom 'mmm' and
use

=IF(DAY(A1)<21,A1,A1+11)

 

[David McRitchie]

You are going in the wrong direction, and not all months have the
same number of days.

"Bryan Hessey" ... wrote...

 

[Bryan Hessey]

Dave,

many thanks, I had missed the direction, the formula could be

=IF(DAY(A1)<21,A1-20,A1)

to go the correct direction.

I considered the number of days per month in my first response (which
also went in the wrong direction) but they appear irrelavant in this
case, just so long as you finish within the correct month.


again, thanks

 

[David McRitchie]

Hi Bryan,
Yes that's it, and you're right it is simpler than mine.

 

[Bryan Hessey]

David,

I think I learn as much from these answers as from anywhere, your use
of the 'true/false' 0/1 was something I wouldn't have known in this
example.

Cheers

 

[Adam]

Thanks guys, this solve's the prob nicely : )