Making a time planning schedule

Hello!

I am currently working on a time planning schedule (not sure if that's the word, english is not my native language) in excel, and think that a macro would be best for the functionality that I need.
The schedule has a starting time and ending time on each day of the week, which the user enters. What (I guess) the macro should do is do some checks, like calculate the total working-time for the week, and how many hours there are between each shift, and give messageboxes when the times entered does not match the criterias.

I don't know if I should do all this inside a macro but this is what the schedule needs to do:

* Give messagebox when a cell-value changes (not sure if this has to be done everytime a value changes, but right now I have this one fixed)
* Count the time until the next shift (ie [starting time of next shift]-[ending time of this shift])
* Find the weekly maximum rest between shifts.

I don't expect somebody to just do the whole macro for me, but any tips or help would be greatly appreciated!

Originally Posted by hamjam

1. user enters starting times and ending times every day - should be easy. Would they enter them as time (eg. 09:40, 17:20) or as numbers

2. check 1 - work out total working time for the week. You could enter a formula that subtracts the daily work times and adds them up. If your times are in time format, you could try a conversion like =HOUR(cell)+MINUTE(cell)/60 for each start and end times

3. check 2 - how many hours between shifts ? Is 1 shift = 1 day ? If not, what is a shift or are there multiple shifts per day, each with its own start and end times ?

4. check 3 - messagebox for cell value changes ? Which cell value ?

5. need more information on the shifts, and how they relate to daily work time and start and end times

1. A possibility to enter the time in multiple ways would be good. I havent gotten very clear instructions on this but I would guess as 09:45 and also as 9.75 (i.e minutes as decimals).

2. If time is entered as 09:45, would the HOUR and MINUTE functions still return the right thing (unsure if this is time format)?

3. Yes, one shift per day, with it's own starting and ending time.

4. The cells which holds the starting and ending time, I want the user to get a messagebox when he or she enters a time that doesn't match the criterias of the schedule.

5. One shift is one day's work, like if you would work 9:00-17:00 on monday, that would be one shift with a length of 8 hours.

Hope this makes things clearer!