• 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.

help with '1/1/2012...

K

konijay

New Member
Hi,


I am using a formula

=sumifs(x:x,y:y,">'1/1/2012")

to sum up the amounts after 1/1/2012


However, the date column(y:y) contains an apostrophe in front of the date which stops excel from reading as a date. Actually, whole worksheet contains the apostrophe except the amount column.


Without creating a whole new column using =datavalue to convert it to DATE and have the formula look at the column, is there a way to do it straight from the above formula?


thanks
 
konijay - the easy way I would approach it...


If your date column is static (meaning you don't need this to work automatically as you add new dates with apostrophes), I would use the Text to Columns tool (found under the data tab) using "Delimited" option to remove the apostrophes from the date. Then use the formula as you've shown but adjust to target the new date column.
 
the thing is I am not importing data...it's an SAP report that gets "refreshed" all the time..so I can't really seperate the apostrophe. Once the report is refreshed, it overwrites any editing you've made.
 
konijay - understood. I know you can have SAP automatically remove that formatting, but if you cannot...


You can create a helper column that will look at all the date cells and use the DATEVALUE function to convert.


Choose any column and use the following formula: =Datevalue(A1)...where A1 is the one of your apostrophe dates. Drag this formula down through the column so that you've got a clean date for all of your values. Now, you can use the same formula to sumif: =SUMIF(C:C,">"&DATE(2012,1,1),B:B)...where C:C is the new date column, and B:B is the column you want to sum.
 
Hi ,


Try this :


=SUM(IF(DATEVALUE(Dates_List)>DATEVALUE("1/1/2012"),X5:X100))


entered as an array formula , using CTRL SHIFT ENTER.


Here Dates_List is your range of dates entered with a leading apostrophe , say Y5:Y100 , and X5:X100 contains your amounts.


Narayan
 
First, your original formula contains another error. Writing ">'1/1/2012" is not doing a date comparison, it's just a text comparison. On the other hand, we don't want to just write ">1/1/2012" as that is actual taking a math operation of 1 divided by 1 divided by 2012. That said, let's get you a solution:


=SUMPRODUCT(--(DATEVALUE(Y2:Y1000)>DateCell),X2:X1000)


Where DateCell is the cell reference for the cell containing a date. If you really want to write the criteria into the formula:

=SUMPRODUCT(--(DATEVALUE(Y2:Y1000)>DATEVALUE("1/1/2012")),X2:X1000)
 
You must log in or register to reply here.
Back
Top