• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Leap Year in a sales table

A

aggie81

Member
Excel 2013
Column A has the dates from January 1, 2012 to December 31, 2012 beginning in Row 2 formatted as 1/1, 1/2 to 12/31.
Row 1 has the years 2012 to 2015 in columns B - E
The sales data is in a separate table called ARBatch.
I have this to pull in the sales data for each day copied down the year column:
=SUMIFS(ARBatch[AMTENTR],ARBatch[Date],DATE(C$1,MONTH($A2),DAY($A2)))
The problem is that it double pulls in the non leap years Feb 29th using Mar 1st data. 2012 works great because it is a leap year.
I have tried IF statements but can't get any to work.
Any help is appreciated.
Thanks,
Lee
I created a Pivot Table that works great showing the same data but thought I would make a simple sales date table and I can't solve the problem.
 
Since XL will try and "help" us if we keep a number to represent Feb 29, we'll first build a string and then have XL try to convert it back to a date. If it's an invalid date, ie Feb 29, 2015, then we can safely ignore it.

=SUMIFS(ARBatch[AMTENTR],ARBatch[Date],IFERROR(DATEVALUE(TEXT($A2,"dd-mmm-")&C$1),"IGNORE"))
 
I modified your formula to this
=IF(ISNUMBER(DATEVALUE(TEXT($A61,"dd-mmm-")&B$1)),SUMIFS(ARBatch[AMTENTR],ARBatch[Date],DATE(B$1,MONTH($A61),DAY($A61))),0)
and it works! On all the years!
Thanks so much for putting me on the right path. I really appreciate your time and thoughts.
Lee
 
Glad you were able to get it working, and thanks for sharing with us the solution you found. :)
 
You must log in or register to reply here.
Back
Top