Working out if a date/time occurs between sunset and sunrise?

[Trottlips]

Hi

I have a list of custom format date and time value in column "A" which occurs over a five month period ( 00/00/0000 00:00:00). i have the date of the sunrise/sunset in column "B" and the time of the sunrise in column C and the sunset time in column D.

I am wanting to have a column which uses the sunrise/sunset times to separate "night" and "day". So that i can analysis the night values separate to the day values. I could probably use pivot table to do the analysis but i need to assign the day and night value first.

has any one as got any idea , completely lost with this one

Cheers

[daddylonglegs]

Does the date in column B match the date in the same row in column A? If so then try this formula in E2

=IF(AND(A2>=B2+C2,A2<=B2+D2),"day","night")

[Trottlips]

Hi
That produces the value "night" for the first row which is correct but when i copy it down it say that all the values are "night" which is not true

Date, Date Sunrise Sunset
01/10/2012 00:30, 01/10/2012, 06:07:42, 17:41:35, night
01/10/2012 01:30, 02/10/2012, 06:09:29, 17:39:10, night
01/10/2012 02:30, 03/10/2012, 06:11:16, 17:36:45, night
01/10/2012 03:30 , 04/10/2012, 06:13:03, 17:34:21, night
01/10/2012 04:30 , 05/10/2012, 06:14:51, 17:31:56, night
01/10/2012 05:30 , 06/10/2012, 06:16:39, 17:29:32, night
01/10/2012 06:30, 07/10/2012, 06:18:28, 17:27:08, night
01/10/2012 07:30, 08/10/2012, 06:20:17, 17:24:45, night
01/10/2012 08:30, 09/10/2012, 06:22:06, 17:22:22, night
01/10/2012 09:30, 10/10/2012, 06:23:56, 17:19:59, night
01/10/2012 10:30 , 11/10/2012, 06:25:47 17:17:37 night

[daddylonglegs]

Does the date in column B match the date in the same row in column A?

Looks like it doesn't (except in row 1) so that formula won't work - try this version

=IF(AND(LOOKUP(A2,B$2:C$100)<=MOD(A2,1),LOOKUP(A2,B$2:D$100)>=MOD(A2,1)),"day","night")

That assumes that the data in columns B,C and D goes as far as row 100, adjust as required

[Trottlips]

cheers that works great