here's a fun and easy one, I just ain't smart 'nuff

[Skidood]

Hi there...see below that I am computing the time interval from a series of timestamps. If the series covers midnight, I get a negative value which screws me up. How can I get a column matching the blue one? Note, there are 1440 minutes in a day.

Also, other sets of data used in this spreadsheet may not include 12 midnight...I appreciate your help!



Excel 2010 32 bit

	B	C	D	E	F	G
8	time of day	minutes of time	interval (min.)		interval (min.)
9	23:56:00	1436.00
10	23:56:30	1436.50	0.50		0.50
11	23:57:00	1437.00	0.50		0.50
12	23:57:30	1437.50	0.50		0.50
13	23:58:00	1438.00	0.50		0.50
14	23:58:30	1438.50	0.50		0.50
15	23:59:00	1439.00	0.50		0.50
16	23:59:40	1439.67	0.67		0.67
17	0:00:10	0.17	-1439.50		0.50
18	0:00:40	0.67	0.50		0.50
19	0:01:00	1.00	0.33		0.33
20	0:01:30	1.50	0.50		0.50
21	0:02:00	2.00	0.50		0.50
22	0:02:30	2.50	0.50		0.50
23	0:03:00	3.00	0.50		0.50

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Calc​

Worksheet Formulas Cell Formula C10 =([COLOR=rgb(255]HOUR(B10)*60+MINUTE(B10) +SECOND(B10)/60[/COLOR]) D10 =C10-C9 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Worksheet Defined Names Name Refers To Calc!b =Calc!$J$4 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Tetra201]

Try

=MOD(B10-B9,1)*1440

 

[PatOBrien198]

Try:

D10 formula:
=C10-C9 + If(C10 < C9, 1440, 0)

 

[Skidood]

That did it. TY !

Try:

D10 formula:
=C10-C9 + If(C10 < C9, 1440, 0)

 

[Skidood]

That worked too. TY!!

Try

=MOD(B10-B9,1)*1440