How can I compare Excel serial dates WITHOUT converting to mm/dd/yy type dates?

Question

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

Answer 1

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:

Answer 2

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)

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

Answer 3

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

Example Excel 2003