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:

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]

Sunset​

[/td][td]

Close​

[/td][td]

​

[/td][/tr]

[tr][td]

2​

[/td][td]

17:00​

[/td][td]

17:30​

[/td][td]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")[/td][/tr]

[tr][td]

3​

[/td][td]

17:01​

[/td][td]

17:30​

[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

17:15​

[/td][td]

17:30​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

17:16​

[/td][td]

18:00​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

17:39​

[/td][td]

18:00​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

17:40​

[/td][td]

18:30​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

17:59​

[/td][td]

18:30​

[/td][td][/td][/tr]
[/table]

 

[Smokeyham]

This seems way too complicated:

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]

Sunset​

[/td][td]

Close​

[/td][td]

​

[/td][/tr]

[tr][td]

2​

[/td][td]

17:00​

[/td][td]

17:30​

[/td][td]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")[/td][/tr]

[tr][td]

3​

[/td][td]

17:01​

[/td][td]

17:30​

[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

17:15​

[/td][td]

17:30​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

17:16​

[/td][td]

18:00​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

17:39​

[/td][td]

18:00​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

17:40​

[/td][td]

18:30​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

17:59​

[/td][td]

18:30​

[/td][td][/td][/tr]
[/table]

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.