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 comma delimited file that includes two columns date/time (which imports as Excel's mm/dd/yyyy hh:mm custom format) and status of 1 or 0. The status represents a piece of equipment either being on or off. I'm trying to generate a graph that will show, hours up vs. down by day.

CONSIDER:

1/1/2012 00:00, 1
1/1/2012 03:00, 0
1/1/2012 14:00, 1
1/3/2012 00:00, 0

This tells me that the equipment was up for three hours, down for eleven hours, and then up for thirty-four hours (across two calendar days). However, I would like to generate a graph that shows how many hours PER DAY we were up or down.

CONSIDER:

1/1 XXXXXXXXXXXXX-----------   (up 13, down 11)
1/2 XXXXXXXXXXXXXXXXXXXXXXXX   (up 24)

To me, it seems that I need to generate a dataset summing HOURS by STATUS by CALENDAR DAY...but I can't seem to find a flavor of pivot table or nested SUM(IF(SUMIF(...))) combination to make it work.

Most troubling is accounting for date changes...in my example above, since my uptime starting at 14:00 on 1/1/2012 crosses midnight, I need to know that 10 uptime hours get totalled with 1/1/2012 and 24 uptime hours get totalled with 1/2/2012.

I may be able to do something with a calendar list to drive the date summation, but then I need a way to compare 01/01/2012 to 01/01/2012 03:00 as equal. There's got to be a way along the lines of if(INTEGER-PORTIONS-OF-SERIAL-DATES-ARE-EQUAL,TOTAL-HOURS-IF-VALUE-IS_1,0) but nothing's worked so far.

Any suggestions? I've been battling this most of the day, and need a fresh perspective.

Thanks

share|improve this question

3 Answers 3

up vote 4 down vote accepted

OMGBBQ this is challenging! I wish I could explain this better, but please bear with me.

My solution requires some additional real estate -- 3 columns to be exact -- and is based on a few assumptions:

  1. The time stamp data would have alternating rows of 1's and 0's, emulating an on/off switch like you described (See Image 1 below).
  2. Time stamps are arranged from oldest to newest.

enter image description here

For simplicity's sake, I'm using whole hours. Though you can add minute-level time stamps if you prefer.

Column E tracks the number of hours left for the day at each time stamp. I used this to "SPLIT" elapsed times that cross midnight.

ON/OFF Columns

Here's the formula in the ON column:

=SUM(IF(AND($B3,A4<>"",INT($A4)=INT($A3)),($A4-$A3)*24,0),
     IFERROR(IF(AND($B3=1,INT($A3)<>INT($A4)),$E3,IF(AND($B2=1,INT($A3)<>INT($A2)),24-$E3,0)),0))

This formula adds two values:

1. The number of hours the switch is ON if the entry isn't the last time stamp for the day; otherwise, 0.

2. The number of hours left until midnight if this is the last time stamp for the day OR the number of hours elapsed since midnight if this is the first time stamp for the day.

The formula in the OFF column is similar, except it checks if Status = 0.

=SUM(IF(AND(NOT($B3),B4<>"",INT($A4)=INT($A3)),($A4-$A3)*24,0),
     IFERROR(IF(AND($B3=0,INT($A3)<>INT($A4)),$E3,IF(AND($B2=0,INT($A3)<>INT($A2)),24-$E3,0)),$B3*($A3-INT($A3))*24))

To extract the DATE from the time stamp column, I used the INT() function. Excel uses integers to represent dates and decimals to represent times (a fraction of a day or of 24 hours). For example, 06/01/2012 18:00 is equal to 41061.75 (18 hours past midnight is 18/24 which is 0.75). I believe this answers your last paragraph.

Consolidating the Hours

Here's the formula in the Hours ON column (See Image Below). It's an array formula, so you need to enter it using Ctrl+Shift+Enter and then copy it down:

=SUM((INT(stamps)=$G3)*hours_on)

In the Hours OFF column:

=SUM((INT(stamps)=$G3)*hours_off)

where
stamps is a Named Range refering to the Time Stamp range (Column A in my example)
hours_on is a Named Range referring to the ON range (Column C)
hours off is a Named Range referring to the OFF range (Column D)

Chart

Notice how for each date, the number of ON and OFF hours sum up to 24.

enter image description here

If you'd like to study the formulas and the workbook, here's a copy: http://db.tt/KZgH7SFV

share|improve this answer

Assuming you have time stamps in A3:A24 and status in B3:B24 as per Kaze's suggestion then you can get the [decimal] hours ON for a date in D3 with this formula

=(LOOKUP(D3+1,A$3:B$24)-SUMPRODUCT((INT(A$3:A$24)=D3)+0,MOD(A$3:A$24,1),B$3:B$24*2-1))*24

no "helper" columns required....

share|improve this answer

It's not pretty, but here's a solution:

After your two columns add a third column defined as day([datecolumn])

       A       B    C  D
   ---------------------
1 | 1/1/2012 00:00, 1, 1
2 | 1/1/2012 03:00, 0, 1
3 | 1/1/2012 14:00, 1, 1
4 | 1/3/2012 00:00, 0, 3
 ...
99| 1/31/2012 11:23, 1,31

Then you can make sum columns defined as

sumif(D1:D99, 1, C1:C99)

Which, in the case above, will show you the sum of the 1's only when column D (previously defined as the "Day" portion of the datetime, is 1. Repeat that formula for each day of the month (the part that I'm not so crazy about), and you should have your values.

Sum of hours on the 1st of the month

sumif(D1:D99, 1, C1:C99)

Sum of hours on the 2nd of the month

sumif(D1:D99, 2, C1:C99)

Sum of hours on the 3rd of the month

sumif(D1:D99, 3, C1:C99)

and so on...

share|improve this answer
1  
I've considered that, but I can't find a formulaic way to cross the date line...in this case, splitting the 34:00 hours beginning on 1/1/12 into 10:00 hours on 1/1/12 and 24:00 hours on 1/2/2012. I could insert additional rows to break similar data at midnight and add a new record, but that kinda defeats the purpose of the automated log. :) –  dwwilson66 Jun 1 '12 at 14:32
    
I'm pretty sure my solution handles the date line issue. Sumif() checks D1:D99 for a certain value (the day portion of the date) and if it resolves to true it sums the appropriate fields. –  nageeb Jun 1 '12 at 15:57

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.