Time array
 

I have this question in an early post "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 inexcel at all? It would be similar to
the one below."

Thanks to T. volko who came up a solution and a formula below:
=IF(COUNT($A1:$B1)<2,"",IF($A1+(COLUMNS($A:A)-1)/24$B1,"",$A1+(COLUMNS($A:A)-1)/24))

Thanks to To Ogilvy who pointed out that I need 9 elements instead of 8.

All work. However, here is the twist. I inserted a row, making all
imformation from above bumped into row2. Starting from C1 which is now a
blank cell I typed 12:00 AM and dragged it across to 11 pm, which is 24
hours. Now, I want to create a time array for row2, which can only visible
or start if a time in row1 match the starting time in cell A1, and it must
end with 1 element less than the array, in which the previous example ended
with 2 pm(9 elements) pm, but now it will end (8 elements) with1pm.
Maybe the below drawing can help explain my question. Can anybody help?
A B C D E
start end 12:00 AM 1:00 AM--6:00 AM
6:00 AM 2:00 PM

Time array
 

you lost me at about 6pm!

"Richard" wrote:


I have this question in an early post "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 inexcel at all? It would be similar to
the one below."

Thanks to T. volko who came up a solution and a formula below:
=IF(COUNT($A1:$B1)<2,"",IF($A1+(COLUMNS($A:A)-1)/24$B1,"",$A1+(COLUMNS($A:A)-1)/24))

Thanks to To Ogilvy who pointed out that I need 9 elements instead of 8.

All work. However, here is the twist. I inserted a row, making all
imformation from above bumped into row2. Starting from C1 which is now a
blank cell I typed 12:00 AM and dragged it across to 11 pm, which is 24
hours. Now, I want to create a time array for row2, which can only visible
or start if a time in row1 match the starting time in cell A1, and it must
end with 1 element less than the array, in which the previous example ended
with 2 pm(9 elements) pm, but now it will end (8 elements) with1pm.
Maybe the below drawing can help explain my question. Can anybody help?
A B C D E
start end 12:00 AM 1:00 AM--6:00 AM
6:00 AM 2:00 PM

Time array
 

In C2 put in the formula
=if(and(C$1=$A2,$C$1<$B2),C$1,"")
then drag fill to the right for 24 columns.

--
Regards,
Tom Ogilvy


"Richard" wrote:


I have this question in an early post "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 inexcel at all? It would be similar to
the one below."

Thanks to T. volko who came up a solution and a formula below:
=IF(COUNT($A1:$B1)<2,"",IF($A1+(COLUMNS($A:A)-1)/24$B1,"",$A1+(COLUMNS($A:A)-1)/24))

Thanks to To Ogilvy who pointed out that I need 9 elements instead of 8.

All work. However, here is the twist. I inserted a row, making all
imformation from above bumped into row2. Starting from C1 which is now a
blank cell I typed 12:00 AM and dragged it across to 11 pm, which is 24
hours. Now, I want to create a time array for row2, which can only visible
or start if a time in row1 match the starting time in cell A1, and it must
end with 1 element less than the array, in which the previous example ended
with 2 pm(9 elements) pm, but now it will end (8 elements) with1pm.
Maybe the below drawing can help explain my question. Can anybody help?
A B C D E
start end 12:00 AM 1:00 AM--6:00 AM
6:00 AM 2:00 PM

Time array
 

Sorry, the cell E1 should be 11 pm. I did not type 24 because there are two
many. So, I used the pointed arrow instead.

"Michael" wrote in message
...
you lost me at about 6pm!

"Richard" wrote:


I have this question in an early post "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 inexcel at all? It would be similar
to
the one below."

Thanks to T. volko who came up a solution and a formula below:
=IF(COUNT($A1:$B1)<2,"",IF($A1+(COLUMNS($A:A)-1)/24$B1,"",$A1+(COLUMNS($A:A)-1)/24))

Thanks to To Ogilvy who pointed out that I need 9 elements instead of 8.

All work. However, here is the twist. I inserted a row, making all
imformation from above bumped into row2. Starting from C1 which is now a
blank cell I typed 12:00 AM and dragged it across to 11 pm, which is 24
hours. Now, I want to create a time array for row2, which can only
visible
or start if a time in row1 match the starting time in cell A1, and it
must
end with 1 element less than the array, in which the previous example
ended
with 2 pm(9 elements) pm, but now it will end (8 elements) with1pm.
Maybe the below drawing can help explain my question. Can anybody help?
A B C D E
start end 12:00 AM 1:00 AM--6:00 AM
6:00 AM 2:00 PM

Time array
 

it started with 6 am alright which is great, but it did not end at 1pm. it
ended at 11 pm.

"Tom Ogilvy" wrote in message
...
In C2 put in the formula
=if(and(C$1=$A2,$C$1<$B2),C$1,"")
then drag fill to the right for 24 columns.

--
Regards,
Tom Ogilvy


"Richard" wrote:


I have this question in an early post "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 inexcel at all? It would be similar
to
the one below."

Thanks to T. volko who came up a solution and a formula below:
=IF(COUNT($A1:$B1)<2,"",IF($A1+(COLUMNS($A:A)-1)/24$B1,"",$A1+(COLUMNS($A:A)-1)/24))

Thanks to To Ogilvy who pointed out that I need 9 elements instead of 8.

All work. However, here is the twist. I inserted a row, making all
imformation from above bumped into row2. Starting from C1 which is now a
blank cell I typed 12:00 AM and dragged it across to 11 pm, which is 24
hours. Now, I want to create a time array for row2, which can only
visible
or start if a time in row1 match the starting time in cell A1, and it
must
end with 1 element less than the array, in which the previous example
ended
with 2 pm(9 elements) pm, but now it will end (8 elements) with1pm.
Maybe the below drawing can help explain my question. Can anybody help?
A B C D E
start end 12:00 AM 1:00 AM--6:00 AM
6:00 AM 2:00 PM

Time array
 

Missed removing one of the $

=IF(AND(C$1=$A2,C$1<$B2),C$1,"")

worked for me.

--
Regards,
Tom Ogilvy


"Richard" wrote:

it started with 6 am alright which is great, but it did not end at 1pm. it
ended at 11 pm.

"Tom Ogilvy" wrote in message
...
In C2 put in the formula
=if(and(C$1=$A2,$C$1<$B2),C$1,"")
then drag fill to the right for 24 columns.

--
Regards,
Tom Ogilvy


"Richard" wrote:


I have this question in an early post "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 inexcel at all? It would be similar
to
the one below."

Thanks to T. volko who came up a solution and a formula below:
=IF(COUNT($A1:$B1)<2,"",IF($A1+(COLUMNS($A:A)-1)/24$B1,"",$A1+(COLUMNS($A:A)-1)/24))

Thanks to To Ogilvy who pointed out that I need 9 elements instead of 8.

All work. However, here is the twist. I inserted a row, making all
imformation from above bumped into row2. Starting from C1 which is now a
blank cell I typed 12:00 AM and dragged it across to 11 pm, which is 24
hours. Now, I want to create a time array for row2, which can only
visible
or start if a time in row1 match the starting time in cell A1, and it
must
end with 1 element less than the array, in which the previous example
ended
with 2 pm(9 elements) pm, but now it will end (8 elements) with1pm.
Maybe the below drawing can help explain my question. Can anybody help?
A B C D E
start end 12:00 AM 1:00 AM--6:00 AM
6:00 AM 2:00 PM

Time array
 

Thanks a bunch Tom. It works.

"Tom Ogilvy" wrote in message
...
Missed removing one of the $

=IF(AND(C$1=$A2,C$1<$B2),C$1,"")

worked for me.

--
Regards,
Tom Ogilvy


"Richard" wrote:

it started with 6 am alright which is great, but it did not end at 1pm.
it
ended at 11 pm.

"Tom Ogilvy" wrote in message
...
In C2 put in the formula
=if(and(C$1=$A2,$C$1<$B2),C$1,"")
then drag fill to the right for 24 columns.

--
Regards,
Tom Ogilvy


"Richard" wrote:


I have this question in an early post "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 inexcel at all? It would be
similar
to
the one below."

Thanks to T. volko who came up a solution and a formula below:
=IF(COUNT($A1:$B1)<2,"",IF($A1+(COLUMNS($A:A)-1)/24$B1,"",$A1+(COLUMNS($A:A)-1)/24))

Thanks to To Ogilvy who pointed out that I need 9 elements instead of
8.

All work. However, here is the twist. I inserted a row, making all
imformation from above bumped into row2. Starting from C1 which is
now a
blank cell I typed 12:00 AM and dragged it across to 11 pm, which is
24
hours. Now, I want to create a time array for row2, which can only
visible
or start if a time in row1 match the starting time in cell A1, and it
must
end with 1 element less than the array, in which the previous example
ended
with 2 pm(9 elements) pm, but now it will end (8 elements) with1pm.
Maybe the below drawing can help explain my question. Can anybody
help?
A B C D
E
start end 12:00 AM 1:00 AM--6:00 AM
6:00 AM 2:00 PM