How to assign dates and times to 'night' or 'day'?

Hi and welcome to the forum!

Is the decision re which times are considered "night" and which are considered "day" in each season coming from you, or do you wish someone here at the forum to decide upon those definitions for you?

If it's coming from you, could you please share them so that we can formulate the necessary solution?

Regards

Still not sure I understand. Are you now saying that you want the degree of accuracy to be based on the sunrise/sunset times for each historical day? And not just an average for each season, which your first post seemed to suggest?

Also, forgive me for my lack of knowledge on the subject, but from your description it would seem that having sunrise/sunset times for just a single year would not be sufficient. I wasn't aware that the sunset/sunrise times at a given location and at a given date in the year were that much different in 10,000BC than they are today. How big are those differences? Big enough to mean that you couldn't use a single year's worth of data and apply it to to all dates from all years?

Either way, the only way you can realistically do this is to reference a table containing the sunrise/sunset times for each period in which you're interested. Once you've got that table, you can use a simple LOOKUP to get the required result for the dates/times of your choice.

Regards

Short but useless answer, I'm sure this is possible. It is difficult to give specific suggestions for a generic question. Here's how I see the solution being developed:

step 0) Determine input type. Are your data stored in Excel as date/time serials or as text strings? I don't work with dates and times in Excel much, so I don't know which will be easier. But I expect that it will be easiest to pick an "input date type" (if you will) and stick with that. This is a good introduction to how Excel handles dates and times as serial numbers that will help inform this decision: http://www.cpearson.com/Excel/datetime.htm#SerialDates
step 1) Based on given date, determine sunrise/sunset times. Again, exactly how to do this depends on the exact format of your sunrise/sunset data. Is it a formula that you use to calculate sunrise/sunset, or is it more of a lookup table? If it is a formula, you will need to understand the formula well enough to get the needed calendar information from the raw data to put into the function and calculate sunrise/sunset. If it is more of a lookup table, you can import that lookup table and use Excel's lookup functions to return sunrise/sunset night/day times: http://office.microsoft.com/en-us/ex...188.aspx?CTT=1 (Vlookup is probably the most commonly used -- especially for beginners to Excel's lookup functions).
step 2) Once we know the desired sunrise and sunset times, we can extract the time from the desired data point and compare it to the sunrise sunset times and determine if it is a "night" or "day" point.

You're welcome. And please come back if you need help with those lookups.

By the way, I'm curious now: is it true that sunrise/sunset times for a given date/location differ much year-on-year?

Regards

I tell you what: why don't you reply to this post when you've obtained the data you want, and hopefully organised it into a nice, simple table, and I'll help you write the necessary lookup formula to obtain your desired results, ok?

Cheers

Ok, thanks.

I notice that each of column A and B contains exactly the same date/time entry, but are just formatted differently (one to show date only, one to show time only), which is fine, though you need to bear in mind that the formula needs a slight tweak because of this, which it wouldn't if you had e.g. just the date in column A and just the time in column B.

As it stands, you could use something like (in C2):

=IF(MOD(B2,1)>INDEX($G$5:$G$16,MATCH(TEXT(A2,"mmm"),$E$5:$E$16,0)),"NIGHT","DAY")

Copy down as required.

Hope that helps.

Cheers

Apologies. Wasn't thinking clearly:

=IF(AND(MOD(B2,1)>=INDEX($F$5:$F$16,MATCH(TEXT(A2,"mmm"),$E$5:$E$16,0)),MOD(B2,1)<=INDEX($G$5:$G$16,MATCH(TEXT(A2,"mmm"),$E$5:$E$16,0))),"DAY","NIGHT")

Regards