Dealing with date, time and subtraction

[kilosub]

Hi All,

How do I compute 10/7/2015 0:02 41023 - 10/6/2015 0:02 40773?

The time I've format it to h:mm. Below is the part of the data which is recorded hourly

a	b	c
10/7/2015	0:02	41,023.00
10/6/2015	23:02	41,012.00
10/6/2015	22:02	41,002.00
10/6/2015	21:02	40,991.00
10/6/2015	20:02	40,980.00
10/6/2015	19:02	40,969.00
10/6/2015	18:02	40,958.00
10/6/2015	17:02	40,947.00
10/6/2015	16:02	40,936.00
10/6/2015	15:02	40,925.00
10/6/2015	14:02	40,914.00
10/6/2015	13:02	40,904.00
10/6/2015	12:02	40,893.00
10/6/2015	11:02	40,882.00
10/6/2015	10:02	40,871.00
10/6/2015	9:02	40,860.00
10/6/2015	8:02	40,849.00
10/6/2015	7:02	40,838.00
10/6/2015	6:02	40,827.00
10/6/2015	5:02	40,816.00
10/6/2015	4:02	40,806.00
10/6/2015	3:02	40,795.00
10/6/2015	2:02	40,784.00
10/6/2015	1:02	40,783.00
10/6/2015	0:02	40,773.00

<tbody>
</tbody>

Thank you and regards,


Norman

 

[oldbrewer]

I do not understand what you want - what does 40773 represent ?

if you start with 6th Oct 2015 00:02 in cell A1
put in A2 =A1+1 it will read 7th Oct 2015 00:02

 

[kilosub]

I have just completed the test data.

Here is the link: https://app.box.com/s/2f7hcv6857lwtq5hwv4ccr8cfu98uyns. If the link is not accessible do let me know.

From the spreedsheet you may see better picture.


Thanks oldbrewer!!
What I would like to capture is daily what's is the total usage. The data capture is on hourly.

 

[kilosub]

Worksheet "Raw"

Date	Time	AHUTB1G01KWH.PV	AHUTB1G02KWH.PV	AHUTB1G03KWH.PV	AHUTB1G04KWH.PV	AHUTB1G05KWH.PV
10/7/2015	0:02	41,023.00	73,272.00	12,051.00	26,405.00	38,013.00
10/6/2015	0:02	40,773.00	72,960.00	11,973.00	26,114.00	37,934.00
10/5/2015	0:02	40,510.00	72,638.00	11,894.00	25,819.00	37,853.00
10/4/2015	0:02	40,247.00	72,312.00	11,816.00	25,523.00	37,768.00
10/3/2015	0:02	39,986.00	71,983.00	11,737.00	25,229.00	37,679.00
10/2/2015	0:02	39,723.00	71,643.00	11,659.00	24,936.00	37,455.00
10/1/2015	0:02	39,463.00	71,302.00	11,580.00	24,644.00	37,173.00
9/30/2015	0:02	39,201.00	70,956.00	11,502.00	24,350.00	36,898.00
9/29/2015	0:02	38,943.00	70,605.00	11,423.00	24,058.00	36,622.00
9/28/2015	0:02	38,683.00	70,259.00	11,345.00	23,764.00	36,345.00
9/27/2015	0:02	38,425.00	69,911.00	11,266.00	23,472.00	36,067.00

<colgroup><col><col><col span="5"></colgroup><tbody>
</tbody>

Worksheet "Daily" Output

	AHUTB1G01KWH.PV	AHUTB1G02KWH.PV	AHUTB1G03KWH.PV
Date	Total	Total	Total
16-Sep-15	252	359	79
17-Sep-15	251	357	79
18-Sep-15	250	359	79
19-Sep-15	250	359	78
20-Sep-15	252	359	79
21-Sep-15	254	358	78
22-Sep-15	254	357	79

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

Using the following formula: =SUM(IF(AND((B3+1)=RAW!A493,TIME(0,2,)=RAW!B493),RAW!C493,0)-(IF(AND(B3=RAW!A517,TIME(0,2,)=RAW!B517),RAW!C517,0)))


worksheet "Monthly" Output

	AHUTB1G01KWH.PV	AHUTB1G02KWH.PV	AHUTB1G03KWH.PV
Month	Total
Sep-15	3,825	5,321	1,180
Oct-15	1,560	1,970	471
Nov-15	0

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

Using the following formula: =SUMPRODUCT((DAILY!$B$3:$B$27<>"")*ISNUMBER(DAILY!$B$3:$B$27)*(MONTH(DAILY!$B$3:$B$27)=9)*(DAILY!$C$3:$C$27))


HTH

 

[oldbrewer]

Date	Time	hut1	hut2	hut3				hut1	hut2	hut3
07/10/2015	00:02	41,023.00	73,272.00	12,051.00			07/10/2015	n/a	n/a	n/a
06/10/2015	00:02	40,773.00	72,960.00	11,973.00			06/10/2015	250	312	78
05/10/2015	00:02	40,510.00	72,638.00	11,894.00			05/10/2015	263	322	79
04/10/2015	00:02	40,247.00	72,312.00	11,816.00			04/10/2015	263	326	78
03/10/2015	00:02	39,986.00	71,983.00	11,737.00			03/10/2015	261	329	79
02/10/2015	00:02	39,723.00	71,643.00	11,659.00			02/10/2015	263	340	78
01/10/2015	00:02	39,463.00	71,302.00	11,580.00			01/10/2015	260	341	79				hut1	hut2	hut3
30/09/2015	00:02	39,201.00	70,956.00	11,502.00			30/09/2015	262	346	78			01/09/2015	8201	10166	3031
29/09/2015	00:02	38,943.00	70,605.00	11,423.00			29/09/2015	258	351	79			01/10/2015
28/09/2015	00:02	38,683.00	70,259.00	11,345.00			28/09/2015	260	346	78
27/09/2015	00:02	38,425.00	69,911.00	11,266.00			27/09/2015	258	348	79
26/09/2015	00:02	38,162.00	69,586.00	11,174.00			26/09/2015	263	325	92
25/09/2015	00:02	37,898.00	69,260.00	11,081.00			25/09/2015	264	326	93
24/09/2015	00:02	37,633.00	68,933.00	10,987.00			24/09/2015	265	327	94
23/09/2015	00:02	37,367.00	68,605.00	10,892.00			23/09/2015	266	328	95
22/09/2015	00:02	37,100.00	68,276.00	10,796.00			22/09/2015	267	329	96
21/09/2015	00:02	36,832.00	67,946.00	10,699.00			21/09/2015	268	330	97
20/09/2015	00:02	36,563.00	67,615.00	10,601.00			20/09/2015	269	331	98
19/09/2015	00:02	36,293.00	67,283.00	10,502.00			19/09/2015	270	332	99
18/09/2015	00:02	36,022.00	66,950.00	10,402.00			18/09/2015	271	333	100
17/09/2015	00:02	35,750.00	66,616.00	10,301.00			17/09/2015	272	334	101
16/09/2015	00:02	35,477.00	66,281.00	10,199.00			16/09/2015	273	335	102
15/09/2015	00:02	35,203.00	65,945.00	10,096.00			15/09/2015	274	336	103
14/09/2015	00:02	34,928.00	65,608.00	9,992.00			14/09/2015	275	337	104
13/09/2015	00:02	34,652.00	65,270.00	9,887.00			13/09/2015	276	338	105
12/09/2015	00:02	34,375.00	64,931.00	9,781.00			12/09/2015	277	339	106
11/09/2015	00:02	34,097.00	64,591.00	9,674.00			11/09/2015	278	340	107
10/09/2015	00:02	33,818.00	64,250.00	9,566.00			10/09/2015	279	341	108
09/09/2015	00:02	33,538.00	63,908.00	9,457.00			09/09/2015	280	342	109
08/09/2015	00:02	33,257.00	63,565.00	9,347.00			08/09/2015	281	343	110
07/09/2015	00:02	32,975.00	63,221.00	9,236.00			07/09/2015	282	344	111
06/09/2015	00:02	32,692.00	62,876.00	9,124.00			06/09/2015	283	345	112
05/09/2015	00:02	32,408.00	62,530.00	9,011.00			05/09/2015	284	346	113
04/09/2015	00:02	32,123.00	62,183.00	8,897.00			04/09/2015	285	347	114
03/09/2015	00:02	31,837.00	61,835.00	8,782.00			03/09/2015	286	348	115
02/09/2015	00:02	31,550.00	61,486.00	8,666.00			02/09/2015	287	349	116
01/09/2015	00:02	31,262.00	61,136.00	8,549.00			01/09/2015	288	350	117
31/08/2015	00:02	30,973.00	60,785.00	8,431.00			31/08/2015	289	351	118

<colgroup><col><col span="6"><col><col span="5"><col><col span="4"></colgroup><tbody>
</tbody>

is this like what you need

 

[kilosub]

Spot on, oldbrewer!!

Can the above be break into different 2 worksheet in a one workbook? Can the dates begin with oldest first?

Sorry if I'm asking a little to much..


Thank you very much, sir!!

 

[oldbrewer]

Date	Time	hut1	hut2	hut3				hut1	hut2	hut3
31/08/2015	00:02	30,973.00	60,785.00	8,431.00			31/08/2015	n/a	n/a	n/a
01/09/2015	00:02	31,262.00	61,136.00	8,549.00			01/09/2015	289	351	118
02/09/2015	00:02	31,550.00	61,486.00	8,666.00			02/09/2015	288	350	117
03/09/2015	00:02	31,837.00	61,835.00	8,782.00			03/09/2015	287	349	116
04/09/2015	00:02	32,123.00	62,183.00	8,897.00			04/09/2015	286	348	115
05/09/2015	00:02	32,408.00	62,530.00	9,011.00			05/09/2015	285	347	114
06/09/2015	00:02	32,692.00	62,876.00	9,124.00			06/09/2015	284	346	113				hut1	hut2	hut3
07/09/2015	00:02	32,975.00	63,221.00	9,236.00			07/09/2015	283	345	112			01/09/2015	8228	10171	3071
08/09/2015	00:02	33,257.00	63,565.00	9,347.00			08/09/2015	282	344	111			01/10/2015
09/09/2015	00:02	33,538.00	63,908.00	9,457.00			09/09/2015	281	343	110
10/09/2015	00:02	33,818.00	64,250.00	9,566.00			10/09/2015	280	342	109
11/09/2015	00:02	34,097.00	64,591.00	9,674.00			11/09/2015	279	341	108		8228 calculated by
12/09/2015	00:02	34,375.00	64,931.00	9,781.00			12/09/2015	278	340	107
13/09/2015	00:02	34,652.00	65,270.00	9,887.00			13/09/2015	277	339	106		=SUMPRODUCT(($H$3:$H$39>=$N9)*($H$3:$H$39<$N10)*($I$3:$I$39))
14/09/2015	00:02	34,928.00	65,608.00	9,992.00			14/09/2015	276	338	105
15/09/2015	00:02	35,203.00	65,945.00	10,096.00			15/09/2015	275	337	104
16/09/2015	00:02	35,477.00	66,281.00	10,199.00			16/09/2015	274	336	103
17/09/2015	00:02	35,750.00	66,616.00	10,301.00			17/09/2015	273	335	102		the right hand table now autopopulates
18/09/2015	00:02	36,022.00	66,950.00	10,402.00			18/09/2015	272	334	101		as you add new data to the left hand table
19/09/2015	00:02	36,293.00	67,283.00	10,502.00			19/09/2015	271	333	100
20/09/2015	00:02	36,563.00	67,615.00	10,601.00			20/09/2015	270	332	99
21/09/2015	00:02	36,832.00	67,946.00	10,699.00			21/09/2015	269	331	98		the formula in the empty cell to the left
22/09/2015	00:02	37,100.00	68,276.00	10,796.00			22/09/2015	268	330	97		of the cell marked ###### is
23/09/2015	00:02	37,367.00	68,605.00	10,892.00			23/09/2015	267	329	96
24/09/2015	00:02	37,633.00	68,933.00	10,987.00			24/09/2015	266	328	95		=IF($H40="","",E40-E39)
25/09/2015	00:02	37,898.00	69,260.00	11,081.00			25/09/2015	265	327	94
26/09/2015	00:02	38,162.00	69,586.00	11,174.00			26/09/2015	264	326	93		H40 will only fill if A40 is not blank
27/09/2015	00:02	38,425.00	69,911.00	11,266.00			27/09/2015	263	325	92
28/09/2015	00:02	38,683.00	70,259.00	11,345.00			28/09/2015	258	348	79
29/09/2015	00:02	38,943.00	70,605.00	11,423.00			29/09/2015	260	346	78
30/09/2015	00:02	39,201.00	70,956.00	11,502.00			30/09/2015	258	351	79
01/10/2015	00:02	39,463.00	71,302.00	11,580.00			01/10/2015	262	346	78
02/10/2015	00:02	39,723.00	71,643.00	11,659.00			02/10/2015	260	341	79
03/10/2015	00:02	39,986.00	71,983.00	11,737.00			03/10/2015	263	340	78
04/10/2015	00:02	40,247.00	72,312.00	11,816.00			04/10/2015	261	329	79
05/10/2015	00:02	40,510.00	72,638.00	11,894.00			05/10/2015	263	326	78
06/10/2015	00:02	40,773.00	72,960.00	11,973.00			06/10/2015	263	322	79
07/10/2015	00:02	41,023.00	73,272.00	12,051.00			07/10/2015	250	312	78
						H40>>>>					#####

<colgroup><col><col><col span="3"><col><col><col><col span="5"><col><col span="5"></colgroup><tbody>
</tbody>

 

[oldbrewer]

if you want the right hand table on a new sheet just put =sheet1!C7 instead of =C7

 

[kilosub]

Hi Oldbrewer,

Oops!! Looks like i miss out something...using the formula given it returns 0.

Below I've include part of the actual raw data and the right side is return result as I wanted, which as you provided as above.

Date	Time	AHUTB1G01KWH.PV	AHUTB1G02KWH.PV	AHUTB1G03KWH.PV	AHUTB1G04KWH.PV	AHUTB1G05KWH.PV	AHUTB1G06KWH.PV	AHUTB1G07KWH.PV	AHUTB1G08KWH.PV	AHUTB1G09KWH.PV	AHUTB1G10KWH.PV	AHUTB1G11KWH.PV	AHUTB1G12KWH.PV	AHUTB1G13KWH.PV	AHUTB1G14KWH.PV					AHUTB1G01KWH.PV	AHUTB1G02KWH.PV	AHUTB1G03KWH.PV	AHUTB1G04KWH.PV	AHUTB1G05KWH.PV
10/7/2015	11:02	41,143.00	73,419.00	12,087.00	26,540.00	38,047.00	20,958.00	84,380.00	17,453.00	58,620.00	26,410.00	203	9,903.00	33,733.00	46,216.00				15/09/2015	0
10/7/2015	10:02	41,132.00	73,406.00	12,084.00	26,528.00	38,044.00	20,943.00	84,372.00	17,445.00	58,613.00	26,402.00	203	9,893.00	33,725.00	46,205.00				16/09/2015	0
10/7/2015	9:02	41,121.00	73,393.00	12,081.00	26,516.00	38,041.00	20,928.00	84,363.00	17,437.00	58,606.00	26,394.00	203	9,883.00	33,716.00	46,194.00				17/09/2015	0
10/7/2015	8:02	41,111.00	73,379.00	12,078.00	26,503.00	38,038.00	20,912.00	84,354.00	17,428.00	58,599.00	26,385.00	202	9,873.00	33,708.00	46,184.00				18/09/2015	0
10/7/2015	7:02	41,100.00	73,366.00	12,074.00	26,491.00	38,035.00	20,897.00	84,346.00	17,420.00	58,591.00	26,377.00	202	9,863.00	33,699.00	46,174.00				19/09/2015	0
10/7/2015	6:02	41,089.00	73,352.00	12,071.00	26,479.00	38,032.00	20,882.00	84,337.00	17,412.00	58,584.00	26,368.00	202	9,853.00	33,691.00	46,163.00				20/09/2015	0
10/7/2015	5:02	41,078.00	73,339.00	12,068.00	26,466.00	38,028.00	20,867.00	84,329.00	17,404.00	58,577.00	26,360.00	202	9,843.00	33,682.00	46,153.00				21/09/2015	0
10/7/2015	4:02	41,067.00	73,325.00	12,065.00	26,454.00	38,025.00	20,851.00	84,320.00	17,395.00	58,570.00	26,352.00	202	9,833.00	33,674.00	46,141.00				22/09/2015
10/7/2015	3:02	41,056.00	73,312.00	12,061.00	26,442.00	38,022.00	20,836.00	84,311.00	17,387.00	58,563.00	26,343.00	202	9,823.00	33,665.00	46,130.00				23/09/2015
10/7/2015	2:02	41,045.00	73,299.00	12,058.00	26,430.00	38,019.00	20,821.00	84,303.00	17,379.00	58,556.00	26,335.00	202	9,813.00	33,657.00	46,119.00				24/09/2015
10/7/2015	1:02	41,034.00	73,285.00	12,055.00	26,417.00	38,016.00	20,806.00	84,294.00	17,371.00	58,549.00	26,327.00	202	9,812.00	33,649.00	46,108.00		15/09/2015		25/09/2015
10/7/2015	0:02	41,023.00	73,272.00	12,051.00	26,405.00	38,013.00	20,790.00	84,286.00	17,363.00	58,542.00	26,318.00	202	9,802.00	33,640.00	46,097.00		07/10/2015		26/09/2015
10/6/2015	23:02	41,012.00	73,258.00	12,048.00	26,393.00	38,009.00	20,775.00	84,277.00	17,354.00	58,535.00	26,310.00	202	9,792.00	33,632.00	46,086.00				27/09/2015
10/6/2015	22:02	41,002.00	73,245.00	12,045.00	26,380.00	38,006.00	20,760.00	84,268.00	17,346.00	58,528.00	26,301.00	202	9,783.00	33,623.00	46,074.00				28/09/2015
10/6/2015	21:02	40,991.00	73,232.00	12,042.00	26,368.00	38,003.00	20,744.00	84,260.00	17,338.00	58,520.00	26,293.00	201	9,773.00	33,615.00	46,063.00				29/09/2015
10/6/2015	20:02	40,980.00	73,218.00	12,038.00	26,356.00	38,000.00	20,729.00	84,251.00	17,330.00	58,513.00	26,285.00	201	9,764.00	33,606.00	46,051.00				30/09/2015
10/6/2015	19:02	40,969.00	73,205.00	12,035.00	26,344.00	37,996.00	20,714.00	84,242.00	17,321.00	58,506.00	26,276.00	201	9,754.00	33,597.00	46,040.00				01/10/2015
10/6/2015	18:02	40,958.00	73,192.00	12,032.00	26,331.00	37,993.00	20,699.00	84,234.00	17,313.00	58,499.00	26,268.00	201	9,744.00	33,589.00	46,029.00				02/10/2015
10/6/2015	17:02	40,947.00	73,178.00	12,029.00	26,318.00	37,990.00	20,684.00	84,225.00	17,305.00	58,492.00	26,259.00	201	9,735.00	33,580.00	46,017.00				03/10/2015
10/6/2015	16:02	40,936.00	73,165.00	12,025.00	26,306.00	37,987.00	20,669.00	84,216.00	17,298.00	58,485.00	26,252.00	201	9,725.00	33,572.00	46,006.00				04/10/2015
10/6/2015	15:02	40,925.00	73,151.00	12,022.00	26,293.00	37,983.00	20,654.00	84,208.00	17,290.00	58,478.00	26,244.00	201	9,715.00	33,563.00	45,994.00				05/10/2015
10/6/2015	14:02	40,914.00	73,138.00	12,019.00	26,281.00	37,980.00	20,638.00	84,199.00	17,282.00	58,470.00	26,236.00	201	9,706.00	33,554.00	45,983.00				06/10/2015
10/6/2015	13:02	40,904.00	73,125.00	12,016.00	26,268.00	37,977.00	20,623.00	84,191.00	17,274.00	58,463.00	26,227.00	201	9,696.00	33,546.00	45,972.00				07/10/2015
10/6/2015	12:02	40,893.00	73,111.00	12,012.00	26,256.00	37,974.00	20,608.00	84,182.00	17,265.00	58,456.00	26,219.00	201	9,687.00	33,537.00	45,961.00
10/6/2015	11:02	40,882.00	73,096.00	12,009.00	26,243.00	37,970.00	20,593.00	84,173.00	17,257.00	58,449.00	26,211.00	201	9,677.00	33,529.00	45,950.00
10/6/2015	10:02	40,871.00	73,082.00	12,006.00	26,231.00	37,967.00	20,578.00	84,165.00	17,249.00	58,442.00	26,202.00	201	9,667.00	33,520.00	45,940.00
10/6/2015	9:02	40,860.00	73,069.00	12,002.00	26,218.00	37,964.00	20,562.00	84,156.00	17,241.00	58,435.00	26,194.00	200	9,658.00	33,512.00	45,930.00
10/6/2015	8:02	40,849.00	73,056.00	11,999.00	26,205.00	37,960.00	20,547.00	84,147.00	17,232.00	58,428.00	26,185.00	200	9,648.00	33,503.00	45,920.00
10/6/2015	7:02	40,838.00	73,043.00	11,996.00	26,193.00	37,957.00	20,532.00	84,138.00	17,224.00	58,420.00	26,177.00	200	9,638.00	33,495.00	45,910.00
10/6/2015	6:02	40,827.00	73,030.00	11,993.00	26,180.00	37,954.00	20,517.00	84,130.00	17,216.00	58,413.00	26,169.00	200	9,629.00	33,486.00	45,899.00
10/6/2015	5:02	40,816.00	73,017.00	11,989.00	26,168.00	37,950.00	20,501.00	84,121.00	17,207.00	58,406.00	26,160.00	200	9,619.00	33,478.00	45,888.00
10/6/2015	4:02	40,806.00	73,004.00	11,986.00	26,155.00	37,947.00	20,486.00	84,112.00	17,199.00	58,399.00	26,152.00	200	9,609.00	33,469.00	45,877.00
10/6/2015	3:02	40,795.00	72,991.00	11,983.00	26,143.00	37,944.00	20,471.00	84,104.00	17,191.00	58,392.00	26,143.00	200	9,600.00	33,461.00	45,866.00
10/6/2015	2:02	40,784.00	72,978.00	11,980.00	26,130.00	37,940.00	20,455.00	84,095.00	17,183.00	58,385.00	26,135.00	200	9,590.00	33,452.00	45,855.00
10/6/2015	1:02	40,783.00	72,974.00	11,976.00	26,126.00	37,937.00	20,456.00	84,086.00	17,174.00	58,378.00	26,127.00	200	9,581.00	33,444.00	45,844.00
10/6/2015	0:02	40,773.00	72,960.00	11,973.00	26,114.00	37,934.00	20,440.00	84,078.00	17,166.00	58,370.00	26,118.00	199	9,571.00	33,435.00	45,833.00
10/5/2015	23:02	40,762.00	72,947.00	11,970.00	26,101.00	37,930.00	20,425.00	84,069.00	17,158.00	58,363.00	26,110.00	199	9,561.00	33,427.00	45,822.00

<tbody>
</tbody>

formula which I put in as per your example: =SUMPRODUCT(($T$2:$T$24>=$R12)*($T$2:$T$24<$R13)*($U$2:$U$24))


Thanks again!

 

[oldbrewer]

=SUMPRODUCT(($T$2:$T$24>=$R12)*($T$2:$T$24<$R13)*($U$2:$U$24))
................................................X..........................X

notice anything ?

 

[kilosub]

Hi Oldbrewer,

Ok, checking back the sample and I've replace it $R12 to $A2 and $R13 to $A3, still not getting 289.

Is the 289, compute automatically as I'm not able to see any formula in the spreadsheet after copying out your sample from here.


Sorry again, I'm not that expert in this, still at baby stage...


Thanks...