Hi Tim,

It would be easier to follow if you attached a sample worksheet to the thread but I think the problem is that you are only putting times in the cells (rather than dates and times).

Instead of putting 5:23:00 PM in a cell, put 01/03/2011 5:23:00PM. If all the cells are set up this way then you can easily subtract them from each other and periods which span across multiple dates will automatically be accounted for.

To deal with the Sunrise / Sunset issue, I suggest you keep a separate table which lists the sunrise and sunset times by date. You can then use a VLOOKUP() to retrieve the sunrise and sunset for the relevant date - since you would now be including the dates in the cells (not just the times) you can look up the correct sunrise/sunset dates without a problem.

Once you have this set-up your calculations will become much easier because all of the underlying numbers will be correct. Feel free to attach an example to the thread and I'll help you put some formulas in place to calculate the day/night hours for each record.