ExcelBanter - recognizing dates past End of Month

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - recognizing dates past End of Month (https://www.excelbanter.com/excel-discussion-misc-queries/93911-recognizing-dates-past-end-month.html)

recognizing dates past End of Month

Thank you so much to all for the help, but How would I add the rest of
the formula to calculate past the "end of month" period for 2 months 3
months, 4-6 months, 7-12 months, 2 yrs, 2yrs? my post below explains
the situation a bit further. anotehr problem arises, that the number of
days in the month is constantly changing, how can that be accounted for
and still categorize them as the 2M, 3M, 4-6M, etc.

thank you once again and I greatly appreciate everyones help.

SK

Quote:
Originally Posted by sk81222
I need to create a recognition for a column. for example. i have a list
of dates starting from May 1, 2006 all the way to December 3, 2010.

and the dates repeat as well.

example:

05/01/06
05/01/06
05/01/06
05/01/06
05/02/06
05/02/06
05/02/06

what I need is for the column beside the dates to show me a
categorization of different time periods:

for example. if 0 - 1 days have passed from the original date of May 1,
2006, then i need it to say O/N , if 2-7 days have passed since May 1,
2006, then i need it to say "2-7 days". if 8-15 days have passed, i
need it to say "8-15 days", the tricky part is that I need to see
whether dates fall in between the 16th day of the month and the Last
day of the month, so for example, in May, between May 16 and May 31,
then i need it to say "16 - End of Month". the problem is that months
have different amounts of days and i cant figure out how to capture
this part.

after that i categorize the sections as Month 2 if the date falls in
between June 1 June 30, then son on until for 3 months, 4-6 months,
7-12 months, 1 yr but less than 2 years, and 2 yrs.

i cant use the IF function becuase it only has 7 entries i can use.

Can anyone help me figure this, would i use vlookup, if and possibly
other functions combined? or maybe a macro?

I would appreciate all the help I get.

best regards.

SK

--
sk81222
------------------------------------------------------------------------
sk81222's Profile: http://www.excelforum.com/member.php...o&userid=35389
View this thread: http://www.excelforum.com/showthread...hreadid=551823

recognizing dates past End of Month

make a lookup table on a spare sheet with the numbers 0 to 20000 in
column A. In column B against the 0 and the 1 put "0-1 days"
against the numbers 2 to 7 put "2 - 7 days" and so on....

Against each of your dates, put a formula =date - May 1st. (you will
need to put the date May 1st in a spare cell, say Z1
so formula is =date-$Z$1. Look up this number in the lookup table.

--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=551823

recognizing dates past End of Month

then make another lookup table that tells you how many days in each
month. does this help?

--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=551823

recognizing dates past End of Month

Thank you

I will attempt it and update.

SK

--
sk81222
------------------------------------------------------------------------
sk81222's Profile: http://www.excelforum.com/member.php...o&userid=35389
View this thread: http://www.excelforum.com/showthread...hreadid=551823

recognizing dates past End of Month