Dates to chart box number

March 30, 201114 yr

Long time lurker. Always found answers before by search. Can not this one. If in wrong place my forgive and tell me where to take it.

File with reservation records, start date and end date. Find group of records and export to chart program. Program colors duration per record. It has 10 columns meaning 10 years with 12 boxes each column meaning months for 120 boxes total. I need to change dates to right numbers in this grid. Here is stuck. Samples are

ResID 24 start 3/15/2011 end 9/22/2011

ResID 66 start 5/19/2011 end 2/14/2012

Start of chart is oldest year of records so 201100. this must be first line of export not my idea and it is done easy. All following lines are the records

201100

ResID 24 (tab) 3 (tab) 9

ResID 66 (tab) 5 (tab) 10?

The program take start year 201100 and fills in next 10 years in column last being 2020 and colors boxe months by my spec. Straight math, right? Nope or my math stinks probably. I convert dates to number (found on this forum) like (Year(StartDate) + Month(StartDate)/100) * 100 which makes then number then subtract. It works in first example but breaks in second (tells it as 97) and if start date isn’t in first year, I can’t figure also.

What box number would 201402 be on chart if chart start is 2011? I see numbers don’t start over at 12 but I can't just divide by 12 either. I don’t know how to fix. Should I change and not use number yearmonth idea at all? major head scratch

Appreciated advance help

Andrew

I think I found answer. don't use my method of year month. I went there because of the chart yearmo example. I think I can get start as

(Year(StartDate)-yearchart)*12+Month(StartDate) and same with end. If better ways please tell me anyway. I haven't checked this in many cases. Thing is, I worked on this problem for three days and post and figure answer in an hour? go figure :)