find a year in a string of dates
 

I have a column of dates that span across several years in column C (1/2/2009)
format and I have hours to complete the project in column Q. I want to make a
cell, A21, where the user can input a year (2009) and pull all the data for
that year which is the first part of the formula below but I can't seem to
get it to recognize just that year. I have tried a combination of different
things but without success. Can someone see the error of my ways??

=IF('Hours Worksheet'!$C$4:$C$1464=$A$21,SUMIF('Hours Worksheet'!$D$4:$D$1464,
26,'Hours Worksheet'!$Q$4:$Q$1464),0))

Thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200905/1

find a year in a string of dates
 

Try this:

=SUMPRODUCT(--(YEAR('Hours Worksheet'!$C$4:$C$1464)=$A$21),--('Hours
Worksheet'!$D$4:$D$1464=26),'Hours Worksheet'!$Q$4:$Q$1464)


--
Biff
Microsoft Excel MVP


"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:95a3933080f14@uwe...
I have a column of dates that span across several years in column C
(1/2/2009)
format and I have hours to complete the project in column Q. I want to
make a
cell, A21, where the user can input a year (2009) and pull all the data
for
that year which is the first part of the formula below but I can't seem to
get it to recognize just that year. I have tried a combination of
different
things but without success. Can someone see the error of my ways??

=IF('Hours Worksheet'!$C$4:$C$1464=$A$21,SUMIF('Hours
Worksheet'!$D$4:$D$1464,
26,'Hours Worksheet'!$Q$4:$Q$1464),0))

Thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200905/1

find a year in a string of dates
 

It works, Thanks! I guess I need to practice with the SUMPRODUCT formula some
more...:)

T. Valko wrote:
Try this:

=SUMPRODUCT(--(YEAR('Hours Worksheet'!$C$4:$C$1464)=$A$21),--('Hours
Worksheet'!$D$4:$D$1464=26),'Hours Worksheet'!$Q$4:$Q$1464)

I have a column of dates that span across several years in column C
(1/2/2009)
[quoted text clipped - 12 lines]

Thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200905/1

find a year in a string of dates
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:95a460c0a0c5c@uwe...
It works, Thanks! I guess I need to practice with the SUMPRODUCT formula
some
more...:)

T. Valko wrote:
Try this:

=SUMPRODUCT(--(YEAR('Hours Worksheet'!$C$4:$C$1464)=$A$21),--('Hours
Worksheet'!$D$4:$D$1464=26),'Hours Worksheet'!$Q$4:$Q$1464)

I have a column of dates that span across several years in column C
(1/2/2009)
[quoted text clipped - 12 lines]

Thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200905/1