|
Formula Help Please
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=551654 |
Formula Help Please
=IF(AND(MONTH($A$1)=MONTH(A1),A1-$A$115),"16 - End of
Month",LOOKUP(A1-$A$1,{0,2,8},{"O/N","2 - 7 days","8 - 15 days"})) what happens when the month moves on? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "sk81222" wrote in message ... 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=551654 |
Formula Help Please
With 5/1/2006 in A1, put this in B2 and copy down..........
=LOOKUP(A2-$A$1,{0,2,8,16},{"o/n","2-7","8-15","End of Month"}) Vaya con Dios, Chuck, CABGx3 "sk81222" wrote in message ... 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=551654 |
Formula Help Please
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? thank you once again and I greatly appreciate everyones help. SK sk81222 Wrote: 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=551654 |
Formula Help Please
did you try my macro?
-- Don Guillett SalesAid Software "sk81222" wrote in message ... 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? thank you once again and I greatly appreciate everyones help. SK sk81222 Wrote: 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=551654 |
Formula Help Please
this seems to work for a continuing formula
=IF(EOMONTH(A2,0)-A2<16,"EOM",LOOKUP(DAY(A2),{0,2,8},{"o/n","2-7","8-15"})) -- Don Guillett SalesAid Software "CLR" wrote in message ... With 5/1/2006 in A1, put this in B2 and copy down.......... =LOOKUP(A2-$A$1,{0,2,8,16},{"o/n","2-7","8-15","End of Month"}) Vaya con Dios, Chuck, CABGx3 "sk81222" wrote in message ... 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=551654 |
| All times are GMT +1. The time now is 04:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com