Adjust date range year

[bademployee]

Hi all

Have been using:

=SUM(Journal!$BY$9:$BY$504)-(SUMIF(Journal!$J$9:$J$504,">31/01/2014",Journal!$BY$9:$BY$504)+SUMIF(Journal!$J$9:$J$504,""<1/1/"2014",Journal!$BY$9:$BY$504))

I have to remember to change the year each year....any ideas how to overcome this?

The user inputs the year in "Journal!V4" on another sheet if it's of any help.

Thanks for any help in advance.

Mark

 

[maheshmaxi]

Which date range do you need to exclude whether from 1st Jan '14 to 31st Jan '14? Can you pls make it clear pls?

 

[Peter_SSs]

Try

=SUM(Journal!$BY$9:$BY$504)-(SUMIF(Journal!$J$9:$J$504,">"&DATE(Journal!V4,1,31),Journal!$BY$9:$BY$504)+SUMIF(Journal!$J$9:$J$504,"<"&DATE(Journal!V4,1,1),Journal!$BY$9:$BY$504))

But you could also try the following alternatives, the first of which requires Excel 2007 or later.

=SUMIFS(Journal!$BY$9:$BY$504,Journal!$J$9:$J$504,">="&DATE(Journal!V4,1,1),Journal!$J$9:$J$504,"<="&DATE(Journal!V4,1,31))

=SUMPRODUCT(--(TEXT(Journal!$J$9:$J$504,"mmm-yyyy")="Jan-"&Journal!V4),Journal!$BY$9:$BY$504)

 

[bademployee]

Try

=SUM(Journal!$BY$9:$BY$504)-(SUMIF(Journal!$J$9:$J$504,">"&DATE(Journal!V4,1,31),Journal!$BY$9:$BY$504)+SUMIF(Journal!$J$9:$J$504,"<"&DATE(Journal!V4,1,1),Journal!$BY$9:$BY$504))

But you could also try the following alternatives, the first of which requires Excel 2007 or later.

=SUMIFS(Journal!$BY$9:$BY$504,Journal!$J$9:$J$504,">="&DATE(Journal!V4,1,1),Journal!$J$9:$J$504,"<="&DATE(Journal!V4,1,31))

=SUMPRODUCT(--(TEXT(Journal!$J$9:$J$504,"mmm-yyyy")="Jan-"&Journal!V4),Journal!$BY$9:$BY$504)

Wow!......certainly know your way around a spreadsheet

Thanks Peter, the third option was perfect.

Appreciate your help

Mark

 

[Peter_SSs]

Thanks Peter, the third option was perfect.

Glad it worked for you. Thanks for letting us know.