creating an array

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Richard - 15 Mar 2007 06:16 GMT

I want to create a time array. For example, cell A1 is the starting time of
6:00 AM, and cell B1 which contains the ending time of 2 PM. From 6 am to 2
pm is exactly 8 hours. I want to create an array of 8 elements with the
starting value of 6:00 AM, and the last value in the array with the value of
2 pm. Of course, the array will start from cell C1 to J1 or 8 elements
cell. Is there a formula to do? or is it possible to do this in excel at
all? It would be similar to the one below.

A B C D E
F G --->>> J
6:00 AM 2:00 PM 6:00 AM
2:00 PM

Reply to this Message

Richard - 15 Mar 2007 06:32 GMT

ignore the drawing below, it is messed up.

>I want to create a time array. For example, cell A1 is the starting time
>of 6:00 AM, and cell B1 which contains the ending time of 2 PM. From 6 am
[quoted text clipped - 7 lines]
> G --->>> J
> 6:00 AM 2:00 PM 6:00 AM 2:00 PM

Reply to this Message

Sox - 15 Mar 2007 06:37 GMT

Richard,

Try the TIME function, which takes the values (hours,minutes,seconds), for
example: = TIME(1,0,0) is one hour.

Then use the TIME function to add one hour to subsequent entries. For your
example:

A1 6:00 AM

B1 =A1 + TIME(1,0,0)

C1 =B1 + TIME(1,0,0)

...etc.

For a worksheet, simplfy the entries by just copying the formula in cell B1
across to add one hour to subsequent cells.

Regards,

Sox

Reply to this Message

T. Valko - 15 Mar 2007 06:39 GMT

Try this:

Enter this formula in C1 and copy across until you get blanks:

=IF(COUNT($A1:$B1)<2,"",IF($A1+(COLUMNS($A:A)-1)/24>$B1,"",$A1+(COLUMNS($A:A)-1)/24))

Format the cells as TIME

Biff

Reply to this Message

Tom Ogilvy - 15 Mar 2007 14:06 GMT

1 6 AM
2 7 AM
3 8 AM
4 9 AM
5 10 AM
6 11 AM
7 12 PM
8 1 PM
9 2 PM

If you want to start with 6 and end with 2, you need 9 elements.

Signature

Regards,
Tom Ogilvy

> I want to create a time array. For example, cell A1 is the starting time of
> 6:00 AM, and cell B1 which contains the ending time of 2 PM. From 6 am to 2
[quoted text clipped - 8 lines]
> 6:00 AM 2:00 PM 6:00 AM
> 2:00 PM

Reply to this Message