Formula to Select Column with Current Date; Works Only for 2014--Why?!

thurberm

Board Regular
Joined
Dec 26, 2013
Messages
50
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:

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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
is it not related to

CY_1 being in your formula

if had been extended to pick up the next two years ??
=TEXT(WEEKDAY(DATE(CalendarYear,1,1),1),"aaa")</pre>
 
Upvote 0
is it not related to

CY_1 being in your formula

if had been extended to pick up the next two years ??
=TEXT(WEEKDAY(DATE(CalendarYear,1,1),1),"aaa")

Not sure what you mean. "CalendarYear" was a named range in the worksheet when it only covered one year, and it's value was "2014" in that one-year worksheet.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top