Sun and Moon states

Spindoctor

New Member
Joined
May 19, 2009
Messages
3
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?
 

Some videos you may like

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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Jan 18, 2005
Messages
7,292
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
Joined
May 19, 2009
Messages
3
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
Joined
Jul 21, 2002
Messages
73,092
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,102,304
Messages
5,486,070
Members
407,531
Latest member
WalterR01

This Week's Hot Topics

Top