DATEVALUE for an array

[daryl83]

Hi All,

I'm looking to perform a SUMPRODUCT based upon the number of weeks passed. However the week column is in the form of a string (WE - 15/06/2019). So I'm looking Sum weeks inc. last weeks using an array but the DATEVALUE and array part is causing an issue = #VALUE!

Please Login or Register  to view this content.

Any tips appreciated

[CK76]

I'd recommend uploading sample workbook with desensitized sample of your data and expected result.

As well, text manipulation of string date can be tricky. What may work in one system may not work in another.

For an example, your formula should work for UK based system (system date format set to dd/mm/yyyy), but won't for US based system (mm/dd/yyyy).

Best practice is to store date as date value and use formatting to display.

If that's not feasible, another alternative is to use helper column. It will often be easier to maintain, and will speed up calculation process.

To attach workbook. Use "Go Advanced" and follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.

[FDibbins]

Maybe try just adding a helper column to extract the datevalue or week num, then using that in the calc?

[XOR LX]

Hi,

You must have some entries in the WEEK column which return an error when passed to DATEVALUE. Any blank cells, for example, would do just that.

If it's not blanks which are the issue, I suggest you temporarily add an additional column to your data table which uses the same =DATEVALUE(RIGHT(...,10) formula on each of the entries in the WEEK column to see which is giving an error.

Regards

[XLent]

I'd also say that if the text formatting of the dates (i.e. 00/00/0000) is the same you probably don't need to coerce the values (via DATEVALUE).

=SUMPRODUCT((LEFT(BUDEPT,4)=LEFT(B11,4))*(PLAN="RF")*(METRIC="FP&P Sls Val")*(RIGHT(WEEK,10)<=(RIGHT(C4,10)))*(DATA))/1000

[XOR LX]

I'd also say that if the text formatting of the dates (i.e. 00/00/0000) is the same you probably don't need to coerce the values (via DATEVALUE).

Your "probably" suggested you had doubts!

Unlike e.g. COUNTIF, SUMPRODUCT does not implicitly treat text strings representing dates as actual dates.

So, for example, your construction will mistakingly consider 01/11/2019 to be 'less than or equal' to 03/10/2018, since it is employing a straightforward text-to-text comparison.

Regards

[daryl83]

Thanks all for your suggestions. In end, just to get the thing done, I've just done a helper column with an IF statement and then added when Helper column = "PAST" into the SUMPRODUCT.