These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Tech help -- excel 2010 + contracts.xml + sumifs + dates
Author
Previous Topic Next Topic
Rion Deteisan
Forged Prophets
#1 - 2011-09-26 23:45:13 UTC  |  Edited by: Rion Deteisan
I'm trying to make excel add up the price of some contracts between 2 dates. (ie. the total value of items i sold within a month). I use the following code:

Quote:
=sumifs('My Contracts'!W:W, 'My Contracts'!V:V,> 'Sheet1'!A1,'My Contracts'!V:V,<= 'Sheet1'!A2)

where:
'Sheet1'A1 = the start date (12/31/2011)
'Sheet1'A2 = the end date (01/31/ 2011)


My equation is a bit more involved with "ifs", However, excel says i have a problem with the formula when i add the above string...

Is it something to do with the formatting of the date in the .xml?

Is my equation incorrect?

.
Fubar
Eagle Eye Inc.
#2 - 2011-09-27 01:55:31 UTC
The following should work...

Quote:
=sumifs('My Contracts'!W:W, 'My Contracts'!V:V,">"& 'Sheet1'!A1,'My Contracts'!V:V,"<="& 'Sheet1'!A2)


You need to combine (concatenate) the greater than and the less than or equal signs (note the quotation marks) to the cells that contain the dates. The & character concatenates the two parts together.
Rion Deteisan
Forged Prophets
#3 - 2011-09-27 17:02:42 UTC  |  Edited by: Rion Deteisan
Fubar wrote:
The following should work...

Quote:
=sumifs('My Contracts'!W:W, 'My Contracts'!V:V,">"& 'Sheet1'!A1,'My Contracts'!V:V,"<="& 'Sheet1'!A2)


You need to combine (concatenate) the greater than and the less than or equal signs (note the quotation marks) to the cells that contain the dates. The & character concatenates the two parts together.


I did as above and I'm still having trouble. I no longer have issues with the error message, but the formulas are not summing the numbers as it should. Output is zero, even though i can confirm that based on the criteria should result in a large number.

I believe the formatting of the date and time might be the issue ( ie. 2011-09-15 23:05:14 ). I tried to format a cell to change the way the date appears and i get no response.



EDIT:

It is something to do with the dates. Excel wont let me format the dates provided by ccp into any other format. However, I can delete the dates and retype them by hand and suddenly the formula WORKS.

whats going on here?

.