Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time array | New Users to Excel | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
Use array to return array of values | Excel Worksheet Functions | |||
Goal Seek On Members of an Array within Array | Excel Worksheet Functions |