Let me start by saying that I'm pretty much a MS office amateur--everything I've learned comes from self-study, Google, and the kind assistance of members of forums like this one. The problem and fix here is likely something simple, but for the life of me, I just can't see it.
The worksheet in question is an aircrew scheduling rainbow. Pic of the parts I need help with might help you to visualize: http://i241.photobucket.com/albums/ff78/thurberm/Multi-yearRainbowScreenshot.jpg.
Columns A through L contain names and other data and are frozen to stay visible when scrolling the columns to the right. The worksheet is designed to allow the user to color code cells in the calendar row/columns to the right of the crewmember name with specific colors denoting status, everything else is locked/hidden. A macro that counts those colored cells provides data for the stats in columns C through L. Some stats need to count specifically colored cells backwards from the current date, others forward.
To make many of the stats counters work, I need to find the column associated with today's date so that I can feed that to the color counting macro as one end of a row range. This is what I'm having trouble with now. Back to that in a second.
This worksheet used to cover only the current calendar year, and everything worked perfectly. There were 365 columns after column L, each representing the corresponding day of the year. Row 6 contains the date corresponding to the appropriate day of the year formatted to display as the three-letter day of the week, like so:
"CalendarYear" was a named range that is pulled from another cell. The net effect is that row 6 displays the correct 3-letter weekday associated with the day, month, and year. That all worked great. Row 7 contains no formula, just the number you see in each cell.
Since I can't leave well enough alone, I've now updated this worksheet to display 3 contiguous years instead of just the current one. There are now 730 more columns after the original 365, and the formula in row 6 is now:
"CY_1" is a named range that is pulled from another sheet where the user enters the years he wants the workbook to cover. In this version, CY_1 = 2014, CY_2 = 2015, and CY_3 =2016. These named ranges are used to display the correct year in various places in the worksheet, including in the formulas shown here. This modified formula still works great and correctly displays the 3-letter weekday associated with the day, month and year for all three years in the updated worksheet.
Now, back to the problem: In the pic, you can see cell "APR9" is highlighted. That's the cell (and the ones below it for each row) that contains the formula that calculates which column is associated with today's date.
In this case, it correctly displays the column associated with November 2, 2014, "LF." You can see the associated formula in the formula bar:
The problem is, when I change my computer's date in MS Win 7 to a future year date, say 15 Jan 2015 or 2016, that cell will show the column associated with 15 Jan 2014 every single time.
All the formulas are based on today's date as set in the computer, so why will the cells with the above formula not show the column associated with the computer's set year, instead of always showing the column associated with the same day and month in 2014?
Thanks in advance for any assistance you all can offer.
The worksheet in question is an aircrew scheduling rainbow. Pic of the parts I need help with might help you to visualize: http://i241.photobucket.com/albums/ff78/thurberm/Multi-yearRainbowScreenshot.jpg.
Columns A through L contain names and other data and are frozen to stay visible when scrolling the columns to the right. The worksheet is designed to allow the user to color code cells in the calendar row/columns to the right of the crewmember name with specific colors denoting status, everything else is locked/hidden. A macro that counts those colored cells provides data for the stats in columns C through L. Some stats need to count specifically colored cells backwards from the current date, others forward.
To make many of the stats counters work, I need to find the column associated with today's date so that I can feed that to the color counting macro as one end of a row range. This is what I'm having trouble with now. Back to that in a second.
This worksheet used to cover only the current calendar year, and everything worked perfectly. There were 365 columns after column L, each representing the corresponding day of the year. Row 6 contains the date corresponding to the appropriate day of the year formatted to display as the three-letter day of the week, like so:
Code:
=TEXT(WEEKDAY(DATE(CalendarYear,1,1),1),"aaa")
"CalendarYear" was a named range that is pulled from another cell. The net effect is that row 6 displays the correct 3-letter weekday associated with the day, month, and year. That all worked great. Row 7 contains no formula, just the number you see in each cell.
Since I can't leave well enough alone, I've now updated this worksheet to display 3 contiguous years instead of just the current one. There are now 730 more columns after the original 365, and the formula in row 6 is now:
Code:
=TEXT(WEEKDAY(DATE(CY_1,1,1),1),"aaa")
"CY_1" is a named range that is pulled from another sheet where the user enters the years he wants the workbook to cover. In this version, CY_1 = 2014, CY_2 = 2015, and CY_3 =2016. These named ranges are used to display the correct year in various places in the worksheet, including in the formulas shown here. This modified formula still works great and correctly displays the 3-letter weekday associated with the day, month and year for all three years in the updated worksheet.
Now, back to the problem: In the pic, you can see cell "APR9" is highlighted. That's the cell (and the ones below it for each row) that contains the formula that calculates which column is associated with today's date.
In this case, it correctly displays the column associated with November 2, 2014, "LF." You can see the associated formula in the formula bar:
Code:
=(SUBSTITUTE(TEXT(ADDRESS($APV9,((TEXT(TODAY(),"")&TEXT((TODAY()-DATEVALUE("1/1/"&TEXT(TODAY(),"yy"))+7),"000"))+6),4),""),"",""))
The problem is, when I change my computer's date in MS Win 7 to a future year date, say 15 Jan 2015 or 2016, that cell will show the column associated with 15 Jan 2014 every single time.
All the formulas are based on today's date as set in the computer, so why will the cells with the above formula not show the column associated with the computer's set year, instead of always showing the column associated with the same day and month in 2014?
Thanks in advance for any assistance you all can offer.