Time array

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

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
 
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.
 
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.
 
it started with 6 am alright which is great, but it did not end at 1pm. it
ended at 11 pm.
 
Back
Top