Databases
StackOverflow2013
Postsdbo
POVBA SUMIFs getting confused about dates - producing incorrect result
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVBA SUMIFs getting confused about dates - producing incorrect result
    text
    copied!<p>The below code is producing the wrong result. On the work sheet we are using two identical date formats (dd/mm/yyyy) however when the following is run it appears to be trying to interpret the <strong>rev_date</strong> as American date format, whilst interpreting <strong>grid_date</strong> as the correct UK format. </p> <p>We tested this by changing the rev_date on the worksheet to the American format, in which case it produces the correct result.</p> <p>Any ideas why we would need to change rev_date to an American format, we would prefer to keep it as UK? </p> <pre><code>Public Function GRIDSALES(rev_date As Date, grid_date As Date) As Double Dim Order_Type As Range Dim Final_Price As Range Dim PaidAlt As Range Dim Excl_Rev As Range Dim PAmount1 As Range Dim PMethod1 As Range Dim PAmount2 As Range Dim PayDate2 As Range Dim PMethod2 As Range Dim Vstatus As Range Dim Team As Range Application.Volatile (True) Set Order_Type = Sheets("KRONOS").Range("$D:$D") Set Final_Price = Sheets("KRONOS").Range("$H:$H") Set PaidAlt = Sheets("KRONOS").Range("$I:$I") Set Excl_Rev = Sheets("KRONOS").Range("$K:$K") Set PAmount1 = Sheets("KRONOS").Range("$O:$O") Set First_PD = Sheets("KRONOS").Range("$Q:$Q") Set PMethod1 = Sheets("KRONOS").Range("$R:$R") Set PAmount2 = Sheets("KRONOS").Range("$T:$T") Set PayDate2 = Sheets("KRONOS").Range("$V:$V") Set PMethod2 = Sheets("KRONOS").Range("$W:$W") Set Vstatus = Sheets("KRONOS").Range("$DL:$DL") Set Team = Sheets("KRONOS").Range("$DO:$DO") GRIDSALES1 = Application.WorksheetFunction.SumIfs( _ PAmount1 _ , Team, "&lt;&gt;9" _ , Vstatus, "&lt;&gt;rejected", Vstatus, "&lt;&gt;unverified" _ , Excl_Rev, "&lt;&gt;1" _ , PMethod1, "&lt;&gt;Credit" _ , PMethod1, "&lt;&gt;Amendment" _ , PMethod1, "&lt;&gt;Pre-paid" _ , First_PD, "&gt;=" &amp; rev_date _ , First_PD, "&lt;=" &amp; Application.WorksheetFunction.EoMonth(grid_date, 0)) GRIDSALES = GRIDSALES1 </code></pre> <p>End Function</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload