selecting exact time (**:00) from 1 minute data

[fakhteh]

hi
i want to select one hour data from sheet 1 some body (Sourabhg98(with best wishes for him)) post me the formula below

A2 =INDEX(Sheet1!A:B,(ROW(A1)-1)*60+2,1)

B2 =INDEX(Sheet1!A:B,(ROW(A1)-1)*60+2,2)
i use it and it works correct until my device has no interruption so som how i face with this problem like this
2011/01/01 02:00 1.223
2011/01/01 02:01 1.217
2011/01/01 02:03 1.228
2011/01/01 02:04 1.212
2011/01/01 02:05 1.21
2011/01/01 02:06 1.204
2011/01/01 02:10 1.211
2011/01/01 02:11 1.214
2011/01/01 02:13 1.218
2011/01/01 02:17 1.222


and then the result is not correct with that formula
so please help me sending a code or formula with sensitiveness to the **:00
thanks so much

[mcmahobt]

I think this may work:

Paste into A2 on your result sheet and confirm with CTRL+SHIFT+ENTER. Drag down until you get an invalid date value:

Please Login or Register  to view this content.

In B2 of the same sheet, paste and drag down to fill. This does not have to be entered with CSE:

Please Login or Register  to view this content.

[fakhteh]

Hi
i dont know if it is my fault or not but the result of (b2=) isnt correct
please check the formula if it needs to be correct and if not warn me
thanks so much

[mcmahobt]

My apologies. Try the formula now.

[fakhteh]

Hi again
i tried the formula and i faced to a problem. how can i fix this : (#value! 2011/01/02 04:00 3.889) as i have attached and the other problem is how can i delete #num! to have just exact time and the last one is the amounth of my data in excel that is too large for about a year (monthly)
so if there is a way as vba (macro) i think it is better if not i will use it afther your correction
i appreciate your help thanks so much.

[mcmahobt]

If you want to go the formula route (processing intensive), paste this into A2 on Sheet2 and confirm with CSE:

Please Login or Register  to view this content.

Then in B2 paste this, does not have to be confirmed with CSE:

Please Login or Register  to view this content.