Calculating imported Data Text Files

I import a log of my internet usage times for a month, using ‘Data – Get external data – Import text file’. The data is start and end times, each range seperated by two blank cells, e.g. data is contained in B5, B6, B9, B10, B13, B14 et seq. C6, C10, C14 calculate the total time (B6-B5+if(B6<B5,1). How can I copy the formulae in C6 to C10 and every subsequent 4th cell down to C882, apart from the laborious individual pastes ? One answer would be to get the data to display on two rows, start times on row B, end times on row C. The formula on row C being copied the length of the row by dragging. The Import text wizard automatically places the data in a column.
MS Office 2000

Calculating imported Data Text Files

Well, in Jindon’s first solution I modified it to
=IF(MOD(ROW(A1),4)=1,B6-B5+IF(B6<B5,1))
The inclusion of “IF” caters for times going through Midnight.
However it returns “False” where there is no valid calculation (B7-B6).
In Domenic’s solution it does not return any “False” but, in both solutions the last result is at C194, C196 et seq display the #value! error. The total hours up to C194 is 12:29:02. The next entry only adds 1:54:35 so why it should not display this I do not know. Also tried manually entering the formula in C194 and dragging down but still got the #value! error (against the “Value if true” equation). Grateful for any further ideas.
Thank you

Calculating imported Data Text Files

Hi jindon

Your parantheses do not match - 5 open 6 closing. Corrected but still get false instead of a blank cell and the paste stops at C194. The main problem now is being unable to drag past C194 without getting #value!

Quote

Jindon

I would not have believed this. b198 is True (Text). b194 is False. Yet b198 was copied from the same scource as b194 and is formatted as custom type h:mm:ss
No matter what I do it remains as Text. Any ideas on this ?

I tried that from Help, it displays 0, clear the contents and copy B194 to B198, still get #value! error, however the istext(b194) returns FALSE.

Solved ! When I imported the data, the wizard shows a preview. I told the wizard to import the date column but not to import the Year column (2004,[space]) followed by H:m:s. The date column does not display 1 as 01 so, when 10 July appears it places the comma and space after 2004 into the H:m:s column thus entering the data as Text. Solved by formatting the date column in Excel to show 2 numerals (01)

Calculating imported Data Text Files

Hi
Presume this is how to do it. Two attachments.
1. Rla01640 is the raw log, save it.
2. Internet Usage.xls is my completed sheet.
Format column A as date, your choice. Format columns B & C as Custom – Type – [h]:mm:ss Enter your formula in C6 – drag down to Row 950. Position cursor at A5.
Clik Data - Get external data – Import textfile. Choose rla01640.txt. Wizard appears. Select “Fixed width” – Start import at row 2 – Clik Next.
Delete the 1st and 3rd columns. Move the third column right one point (at 21).(This eliminates the comma).
Clik – Next. Choose “Do not import” for the 1st column and the 3rd (2004,), clik – Finish. “Existing worksheet” with A5 selected should show. Clik – OK.
My poor eyesight failed to spot the comma followed by a space in column B.
Thanks a lot. Le saluda con fraternal afecto en el Señor.
Garcia. Monk of Solius