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:

- 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).
- Time stamps are arranged from oldest to newest.

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.

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