Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a table that contains a number of values representing Excel serial dates. After a number of unsuccessful attempts to compare fields, my current approach is to do comparisons between serial dates instead of calendar dates. I am trying to summarize the data--by DAY--with formulae.

CONSIDER:

41021       some data
41021.625   some data
41021.63542 some data
41022       some data
41022.26042 some data
41022.91667 some data
41023       some data
41023.375   some data

DESIRED RESULT:

41021       sum of 41021, 41021.625 and 41021.63542 data
41022       sum of 41022, 41022.26042 and 41022.91667 data
41023       sum of 41023 and 41023.375 data

In essence, for all instances of SerialDate.SerialTime, SUM data values associated with SerialDate.* regardless of the *.SerialTime for that date.

While I can see how to do this by creating additional dates column formatted as =TEXT(<DateField>,"mm/dd/yyyy") I'm looking for a solution that will allow me to handle this 'conversion' in the formula, e.g.SUMIF((TEXT(<dateRange>,"yy/mm/dd"),=(TEXT(<dateField,"yy/mm/dd")),<dataRange>

Make sense? Anyone have any ideas?

Thanks

share|improve this question
    
What cell format is assigned to your serial dates? –  kopischke Jun 1 '12 at 14:32
    
@kopischke - right now, they're set as "General". I've tried a number of format variations unsuccessfully. –  dwwilson66 Jun 1 '12 at 14:34

3 Answers 3

up vote 3 down vote accepted

Assuming your serial times are in column A, the associated data in column B, the following formula

=IF(INT($A1)=$A1,SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

will display the sum behind the first integer time value in column C when input in C1 and extended to the column. If that is all you need, you are done.

If, however, you need all your sums one below the other without blank lines in between, you will have to use an array formula and insert an extra column for the reference labels. In column C, select the cells in rows containing data (i.e., if your data fills the rows 1 to 100, select C1 to C100), and insert the following formula in the formula bar (not directly in the cell!):

=IFERROR(SMALL(IF(INT($A1:$A100)=$A1:$A100,$A1:$A100,""),ROW()),"")

Note that for it to work, you need to save it as an array formula by confirming your formula input with Ctrl+Shift+Enter (not Enter – if you did it right, the formula will display enclosed in braces). You also will have to adjust the scope as needed, as the formula returns 0 for empty cells. Finally, note array formulas get rather slo.o.o.o…o…o…w on large datasets.

Once done, you can add

=IF($C1<>"",SUMIFS($B:$B,$A:$A,">="&$A1,$A:$A,"<"$A1+1),"")

to column D and there you are:

Array formula in action

share|improve this answer
    
Nice work....! +1 –  barry houdini Jun 1 '12 at 22:24

If you're trying to summarize data by day (or to get a daily subtotal), you can try this array formula. Enter this in the formula bar and then press Ctrl + Shift + Enter.:

=SUM((INT(datetimes)=D3)*somedata)

Where

  • datetimes contains your serial dates, in either General or mm/dd/yyyy h:mm format
  • somedata contains some numbers to add (B2:B25 in my example below)

enter image description here

Excel sees dates as integers (serial values) and times as decimals (a fraction of 24 hours). For example, 06/01/2012 3:00:00 PM is equal to 41061.625 (which is what you'll see if you change the cell format to General). The whole number represents the date, while 0.625 represents 3:00 pm or 15:00 or 15/24. To extract the DATE segment, you can use INT(A1).

share|improve this answer

The simplest way to do this is use Excel's (2007/2010) built-in Table and Pivot Table features.

  1. Create an Excel table from your data.
  2. Create a helper column for the Date, using Excel's "INT" function, which will roll all values down to the appropriate day (the integer part of your date/time field). You can also easily create a helper column for the Time value of your field.
  3. Create a pivot table using your data table as a source.
  4. Set your row labels as your helper Dates and the pivot values as your data points (and you can add the Time field as your column labels).

Then whenever you update your table, you can refresh your pivot table for current subtotals. As a bonus you can also choose any of the other aggregate functions (min, max, avg) and you can choose to format the pivot table's labels in a traditional date format without affecting your data table values.

If you're using Excel 2003, you can accomplish the same thing using the List function (instead of Tables).

Example Excel 2010

Excel 2010

Example Excel 2003

Excel 2003

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.