conditional formatting/days of the week

I have an odd conditional formatting problem that I'm wondering if anybody can help me out with, I spent the majority of my productive workday working on it yesterday and still couldn't figure it out.

I'm trying to come up with a table that basically has the hours of the day down the left hand side in 30 min. intervals, and the date across the top. I want to use it to plot moonrise/moonset times, with the time that the moon is up being a different color.

Using conditional formatting, I've gotten this to work by basically having it this way:

Let's say it rises at 812h and sets at 1626h. In each cell under the date, I've inserted numbers, 0, 30, 100, 130, 200... representing 0:00, 0:30, 1:00, etc. Then, if the cell value is less than 812 and not greater than 1626, it turns blue. This works in the majority of cases.

The problem comes about when let's say the moon rises at 1826h, but sets at 123h the next day. My chart for that date then becomes inverted and I seriously can't figure out how to do it without plugging in the values manually.

Here is the example, you can see that around Jan. 15th it gets all messed up. Is there any way to do this?

http://www.15sunrises.com/images/January.xls

Note, on January 17th, 2:38 is the time that the January 16th moon sets. Essentially, on the 17th, there would need to be blue between 0:00 and 2:38 (so 3:00) in order for it to be correct.

I can't get this to work whatsoever, I hope that maybe you guys can help me out!

thanks!

Hi,

Your spreadsheet has several problems

- The way you store the time values. You have 11:00pm as 2300 and 2:00am as 200. You may know that the AM value is actually on the next day, but Excel does not.

If you change your numbers to time/date values, It will work better, so use 01/01/2008 0:30 AM for the first of January half past midnight. This date/time value gets stored as a number, which can be compared to other numbers. You can still format the date/time as h:mm, which will then show as 0:30 for half past midnight or as 23:30 for 11:30 PM.

If you consistenly fill your daily calendar range with these time values, then you also need to apply them to the rising and setting times. So not just 23:10 but 15/01/2008 11:10 PM instead. Again, format to show only hours and minutes.

Now for the conditional formatting. You test each day against whether the time displayed in the cell is within the range between rise and set. The problem occurs when the moon sets after midnight. Because in the column for the next day, you check if the 0:30 time slot falls between this day's rise and set times.

What you need to do is to add a second condition. Create a condition exactly like the first one, same colours and such, but change the range to one row less than the first condition.

Example: Your first condition is cell value is between $B$71 and $C$71
Now create another condition with cell value is between $B$70 and $C$70

So, if the moon sets after midnight on a day, the first condition for the 0:30 time slot will check if it falls withing the rise/set period of that new day, which it does not. The second condition will then check if it falls between the rise/set period of the previous day. Bingo! yes it does, and the colouring will apply.

Hope to have made this clear.

attached an example for January 2008 where the second condition is applied around the middle of the month.

NB, your spreadsheet was missing several conditions and had some jumps between days. Hope I found them all.

Teylyn

Hi teylyn,

Thank you very much for your reply. Your solution definitely works, but it poses another problem.

I wanted to use the conditional formatting in a way so that I could distribute this spreadsheet to other people interested in this kind of thing, and then they would only have to plug in the rise/set times for their area, rather than having to change all of the time values, etc. which would be a lot of work.

I use this site: http://aa.usno.navy.mil/data/docs/RS_OneYear.php which then generates a table, which I import into excel, my idea was to have people simply enter their longitude/latitude and would be able to get their results generated.

I'm wondering if maybe I could just convert the excel times to the proper format and perhaps use something like this function:

=TIME(TRUNC(A1,-2)/100,A1-TRUNC(A1,-2),0)

with A1 being the source cell.

I really don't know. I'm willing to put the work into the original spreadsheet so that people don't have to do it in the future, but I'm beginning to wonder if it's even possible!

Thank you very much for the help teylyn. I'll have a look at the spreadsheet when I get home (to an English version of excel!), and let you know how it comes along.