Spin Button for 24hrs time entry

[Martin Currie]

Hello guys

im looking for somw help with a formula to allow me to insert a spin buttom to change the time by 30 minute increments. I have made a simple formula to complete this task but i've come up with a small problem.
When I reach 24:00 my the time changes to 00:30 then 01:00 as it should but this will only happen until the maximum number is reached in the spin box properties.

Is there a formula that can be used to allow the user to keep clicking on the increase or decrease button and the time value will cycle through the 24hrs of the day in the 30 minute increments I want.

The project im working on is an ETA calculator for the ship im working on right now. iI want to use the spin buttons to make data entry as easy as possible for the end user, rather than typing in numerical values. The only numerical value I want them to type is the distance between ports.

I'm new to excel and I have seen some VBA code online, but I'd rather stay away from this as I haven't got the foggiest about VBA Code. To sum it up what I'm looking for is a formula that will loop through the 24hrs of the day in 30min increments if the user was to keep pressing the increase button and vise versa.

Thanks for you time and ive attached the formula that I've been working on so far.

Cheers

Martin

[LJMetzger]

Try the following formula:

Please Login or Register  to view this content.

Lewis

[Kaper]

the spin button do not have "wrap around" property. You can cheet a bit changing the formula into

Please Login or Register  to view this content.

now the upper limit for spinbutton can be very high (for instance 30 000 and you can have starting point of say 15 000). But after same (rather long ;-) time you can "touch the wall" at 0 or 30000.

edit: Ooops, while writing this the identical formula has been proposed - but have a look o max and starting value comment.

[Martin Currie]

Guys

Thanks for the help! I've added the above formula and adjusted the min and max values to a mid point and its working a treat!

Thanks for your help!

Martin