Sun and Moon states

[Spindoctor]

Hi

As you can see I'm a first time poster. I have spent the last 48 hours trying to find help on the below, suceeded in getting some way to the formula but seem to be falling at the last post! I have checked the forum, but can't find anything similar on which to compare.

To give you some background: I'm undertaking a 100km charity walk in a few weeks. Along the way we have checkpoints where we can change kit, refuel etc. In order to work out where we'd be at approximate times i've put together a spreadsheet that totals times based on average speed etc. All that seems to be working fine.

The bit I'm having a challenge with is as follows:

We'll be walking through the night and into the morning. I want to know roughly which stages we'll be walking in the dark. I have placed the time for sunrise in one cell and sunset in another.

In can make the formula work to an extent, but I can't work out how to make it revert back to the 'sun' state after sunrise!
The formula at present is: =IF(G8<=$C$4,"SUN","MOON")

G8 is the time of day we'll be at each checkpoint (formatted hh:mm am/pm)
$c$4 is sunset
$c$5 is sunrise

I'm using Excel 2003. Can anyone provide some assitance?

 

[Andrew Poulsom]

Welcome to the Board.

Try:

=IF(AND(G8>=$C$5,G8<$C$4),"SUN","MOON")

Make sure your times don't include a date.

 

[Domski]

I'm undertaking a 100km charity walk in a few weeks.

A friend of mine is doing a similar thing around the Leeds area.

Best of luck, hope it goes well for you

Dom

 

[Spindoctor]

Thanks for the help and the message of luck - my feet need it. Weirdly, the statements are still not changing when dawn breaks.

I have posted the spreadsheet in question, should you wish to review it and tell me why i'm being such a fool!

http://www.box.net/shared/z8g0yn2319

 

[Andrew Poulsom]

Because your times include a date (from G16 onwards) you need:

=IF(AND(MOD(G8,1)>=$C$5,MOD(G8,1)<$C$4),"SUN","MOON")

 

[Spindoctor]

Total genius. Many thanks, hugely appreciated.

Ben