finding night hours (0-day,1-night) between ranges of data

Hello, I am trying to resolve the problem of finding the night hours.

I have the range of data set for nine years (for each hour) in one column, and in another columns I have for each day: rising and setting time of the sun. What I am trying to get is eg. for each hour in column A, I am trying to get a value 0 (if it is between the rising and setting time)-day or value of 1 if it is a night (between setting and rising time). It should correspond to the same day in column A(that is hourly time step) and B (that is daily time step).


I attached also a draft xlsx file.

Hope that someone can help me to resolve this

Thanks a lot !

Mark

Try this

=--NOT(AND(MOD(A2,1)>=C2,MOD(A2,1)<=D2))

Hi Bob,

the problem i am facing is that column A and B are not the same time step(hour vs day). To say for instance, A25 (it is 24-th hour of the first of January) will still correspond to the cell B2, C2 and D2(where is first of January), and then will do next 24 cells for second of January related only to cell C3,B3,D3.

I do not know how i can fix the dates so I can loop through dates and for each 24 cells in column A, should be fixed for one cell in column b,c,d and so on...

thanks for try