# Sun and Moon states

#### Spindoctor

##### New Member
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?

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### Andrew Poulsom

##### MrExcel MVP
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

##### Well-known Member
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
Total genius. Many thanks, hugely appreciated.

Ben