Calculate Closing Time (Rounded to 1/2 Hour) Based on Sunset Time

[Smokeyham]

Hello,

I am trying to set up a system in Excel 2007 (running under Windows 7) to calculate a facility's closing time based upon "rules" about Sunset time. Using the very helpful information from exceldir I was able to download the Sunset times, and convert them using the formula =TIME(C8,D8,0).

Now I would like to use those times calculate the closing times based on two rules:

Rule #1
If sunset is between 20 minutes before the hour and 15 minutes after the hour, then set close time at the next ½ hour

Example: Sunset is between 5:40 PM and 6:15 PM then close time is 6:30 PM.

Rule #2
If Sunset is between 16 minutes after the hour and 39 minutes prior to the next hour, then set close time at the next full hour.

Example: Sunset is between 7:16 PM and 7:39 PM then close time is 8:00 PM.

Can anyone tell me a formula which would allow this to be calculated?

Thanks for any help!

 

[shg]

This seems way too complicated:

	A​	B​	C​
1​	Sunset​	Close​	​
2​	17:00​	17:30​	B2: =CHOOSE(MATCH(MINUTE(A2), {0,1,16,40}), A2+"0:30", CEILING(A2, "0:30"), CEILING(A2, "1:00"), CEILING(A2, "1:00")+"0:30")
3​	17:01​	17:30​
4​	17:15​	17:30​
5​	17:16​	18:00​
6​	17:39​	18:00​
7​	17:40​	18:30​
8​	17:59​	18:30​

 

[Smokeyham]

This seems way too complicated:

	A​	B​	C​
1​	Sunset​	Close​	​
2​	17:00​	17:30​	B2: =CHOOSE(MATCH(MINUTE(A2), {0,1,16,40}), A2+"0:30", CEILING(A2, "0:30"), CEILING(A2, "1:00"), CEILING(A2, "1:00")+"0:30")
3​	17:01​	17:30​
4​	17:15​	17:30​
5​	17:16​	18:00​
6​	17:39​	18:00​
7​	17:40​	18:30​
8​	17:59​	18:30​

Thanks so much! Exactly what I needed, and I really appreciate learning a new tool.

 

[shg]

You're welcome. I'll be embarrassed if barry houdini shows up.