Convert a Number to a Year

[braydon16]

I have a feeling this is pretty straight forward, I just can't seem to grasp the logic (too late in the day). I have a drop down list (validation list) that allows a user to select a year for the report. This is just a number, so when you convert say, 2012, to a year you get the year 1905; logically. I need it to convert to 2012. Any ideas?

[daddylonglegs]

I'm not sure why you need to convert it at all? If the user is selecting 2012 you already have the year, don't you?

[tlafferty]

Sure - create a 2 column table and name it REPORTDATES
Column 1, years, and point your validation at that. Name the validated cell REPORTYEAR.
Column 2, Jan 1 of that year for each date in the table (ie 01/01/2011)

Formula to return the year: =YEAR(VLOOKUP(REPORTYEAR,REPORTDATES,2,FALSE))

[braydon16]

Here's the caveat, once the user has selected the year, they need to select the first month of the year, which populates the other 11 months. Then, the formula I have in each cell below the month goes out to a separate worksheet and looks for the month and year using a sumproduct function

(=SUMPRODUCT(--('Tracking Log'!$D$6:$D$10000=$C13),--(MONTH('Tracking Log'!$B$6:$B$10000)=COLUMN(E$11)-4),--(YEAR('Tracking Log'!$B$6:$B$10000)=Year))

If in fact, the user is a fiscal year and not a calendar year, the formula can't use the drop down or REPORTDATES function to fill the year. Make sense?

[Haseeb Avarakkan]

Assume in A1 you have validation list of years (4 digit number like, 2010, 2011, 2012 etc....) So try this formula in E11 copy across

=SUMPRODUCT(--('Tracking Log'!$D$6:$D$10000=$C13),--(TEXT('Tracking Log'!$B$6:$B$10000,"yyyym")=$A$1&COLUMNS($E11:E11)))

This will give the count for each month depends the year selected in A1.

[braydon16]

I've attached a sample of the data I'm working with.

On the Cost per Lead sheet, you'll see that there is a drop down to allow the user to select the year. But, if they have a fiscal year (in this case, starting in July) I'm struggling with how to count the leads in the right year. For July through December, the year would be 2011. But, for January through June, the year would be 2012.

[Haseeb Avarakkan]

Assume you are using XL2007 or later. Please update it on your profile.

Try this in E12, copy across.

=COUNTIFS(Log!$D$5:$D$9999,$C8,Log!$B$5:$B$9999,">="&EDATE($E6&Year,COLUMNS($E12:E12)-1),Log!$B$5:$B$9999,"<="&EDATE($E6&Year,COLUMNS($E12:E12))-1)