2 Replies Latest reply: Jun 2, 2011 9:17 PM by 4thSpace
4thSpace Level 1 Level 1 (0 points)

I have the following rows.  This is only a small sample.  The actual data will go for a year at a time usually.

 

Date

Time

Open

Close

04/01/2011

8:35 AM

1020.25

1021

04/01/2011

8:40 AM

1021.25

1022

04/01/2011

8:45 AM

1022.25

1023

04/01/2011

8:50 AM

1023.25

1024

04/01/2011

8:55 AM

1024.25

1025

04/01/2011

9:00 AM

1025.25

1026

04/02/2011

8:35 AM

1027.25

1027.5

04/02/2011

8:40 AM

1028.25

1028.5

04/02/2011

8:45 AM

1029.25

1029.5

04/02/2011

8:50 AM

1030.25

1030.5

04/02/2011

8:55 AM

1031.25

1031.5

04/02/2011

9:00 AM

1032.25

1032.5

04/03/2011

8:35 AM

1031.75

1032

04/03/2011

8:40 AM

1031.25

1031.5

04/03/2011

8:45 AM

1030.75

1031

04/03/2011

8:50 AM

1030.25

1030.5

04/03/2011

8:55 AM

1029.75

1030

04/03/2011

9:00 AM

1029.25

1029.5

 

I'd like to copy out 8:35/open and 9:00/close into new columns along with date.  Time isn't relevant in the new columns.  Below I show how this should look.

 

Date

Time

Open

Close

Date

Open

Close

04/01/2011

8:35 AM

1020.25

1021

04/01/2011

1020.25

1026

04/01/2011

8:40 AM

1021.25

1022

04/02/2011

1027.25

1032.50

04/01/2011

8:45 AM

1022.25

1023

04/03/2011

1031.75

1029.25

04/01/2011

8:50 AM

1023.25

1024

04/01/2011

8:55 AM

1024.25

1025

04/01/2011

9:00 AM

1025.25

1026

04/02/2011

8:35 AM

1027.25

1027.5

04/02/2011

8:40 AM

1028.25

1028.5

04/02/2011

8:45 AM

1029.25

1029.5

04/02/2011

8:50 AM

1030.25

1030.5

04/02/2011

8:55 AM

1031.25

1031.5

04/02/2011

9:00 AM

1032.25

1032.5

04/03/2011

8:35 AM

1031.75

1032

04/03/2011

8:40 AM

1031.25

1031.5

04/03/2011

8:45 AM

1030.75

1031

04/03/2011

8:50 AM

1030.25

1030.5

04/03/2011

8:55 AM

1029.75

1030

04/03/2011

9:00 AM

1029.25

1029.5

 

How can this be accomplished?

  • 1. Re: How to copy these cells?
    Barry Level 7 Level 7 (29,180 points)

    If I'm understanding the example, you have:

     

    Date and Time values in column A, formatted to show Date only.

    Date and Time values in column B, formatted to show Time only.

    Number values in column C.

    Number values in  column D.

     

    Column E is empty.

     

    You want:

     

    F2 to display the date part of the earliest Time and Date value in column A

    F3 to display a date one day later than the date displayed in F2

    etc.

     

    G2 to display the 8:35 AM Open value for the date shown in F2

    G3 to display the 8:35 AM Open value for the date shown in F3

    etc.

     

    H2 to display the 9:00 AM Close value for the date shown in F2

    H3 to display the 9:00 AM Close value for the date shown in F3

    Time and Date value in column A

     

    Here's one way to go about it.

     

    Picture 5.png

    Column A contains Date and Time values, starting with the D&T shown in A2, then incrementing by five minutes until 9:00 AM is reached in row 7. Enter the formula below in A3, and fill it down to A7.

     

    =A2+1/(24*12)

     

    A8 is one day later than A2:

     

    =A2+1

     

    Set  A8 (and A2) to Bold to mark the start of a new day.

     

    Set the cell format for these cells to Date & Time, Date formatted as you choose, Time set to None. (alternately, you can display the full Date and Time value in this column and eliminate column B)

     

    Now select cells A3 to A8, then drag the Fill control down to fill the rest of the column using the same set of formulas (and the Bold setting for the first of each new day).

     

    Column B:

     

    B2:     =A

     

    Fill down.

     

    Columns C and D contain entered data. Rather than meticulously copy your sample data, I've used O and strings of os to marl the Open cells to be returned, and C (cc) to mark the Close cells.

     

    F2, G2 and H2 contain variations of the same OFFSET formula, filled down (to the row showing error triangles):

     

    F2:       =OFFSET($A$1,(ROW()-2)*6+1,0)

    G2:      =OFFSET($A$1,(ROW()-2)*6+1,2)

    H2:      =OFFSET($A$1,(ROW()-2)*6+6,3)

     

    Select all three and Fill down.

     

    The error triangles are a 'bad reference error' caused by the Row offset ( ROW()-2)*6+1 ) pointing to a row not on the table.

     

    Regards,

    Barry

  • 2. Re: How to copy these cells?
    4thSpace Level 1 Level 1 (0 points)

    Perfect Barry.  Works great.  Thank you.