Creating a half-hour schedule that displays the time worked by interval (including breaks)

I am trying to create a half hourly schedule that displays the amount of time worked (in hours) per half hour, including a logic for breaks and lunches. I've attached an example of the format i am using. I'm really having trouble with the formula to output the half hourly values correctly. Also, I'm not set on the time format, you can use decimals if it makes things significantly simpler (IE 17:30 would be 17.5) Any help would be greatly appreciated!

Thanks!

MScheduling.xlsx

Hi SUTTEHFACE,

I am not clear about what your question is, however, I can see that the times you entered into cells E4:F5 are in the wrong format. You need a space before the AM/PM for Excel to recognise the values as time.

Personally, I find entering time a pain in the neck because you have to always enter a colon between the hours and minutes. Therefore, of decimal time is an option, I say "go ahead". Another option is to enter time as a four digit number. In your example - 17:30 would be 1730. You need to consider the end user, and understand how he/she will enter the data.

Having said that, if you use decimal values, you still have to convert the answer back, because in the end, you are calculating time.

What are you trying to achieve with the 24 hours (in half hour steps) in cols H to BD? This uses a lot of screen real estate! I need to gain a better understanding of your needs to assist further.

I hope this helps, please let me know!

Regards,

David

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

When you reply please make it clear WHO you are responding to by mentioning their name.

Scheduling.xlsx

David,

Thank you for your reply. I've attached an updated copy of the file which will hopefully make a little more sense. I am trying to schedule people on a half hour basis and sum all half hour increments to make sure I am staffed properly, based on the demand requirements per half hour. Columns H through BD break down each half our increment and represent how much time someone is expected to work for that half hour increment. So for the 9AM half hour increment, John SMith is expected to work 0.5 (half an hour). But, for the 10.5 (10:30 AM) half hour increment, he is only expected to work 20 minutes, since he will be on break for 10 minutes, hence 0.33. Each column H through BD is then summed for all the individuals and compared to what the minimum and maximum requirements are to ensure I am staffing accordingly. I should also mention that this is for a single day. I plan on repeating this process for the entire week.

Thanks!

M

Sorry M,

This is still quite confusing! I don't understand why you want to go to the trouble of having 48 columns with entries in them.

I would assume that if John Smith was rostered on from 8:30 to 17:00 he worked 8.5 hours less 1 hour and 40 minutes. All those columns are unnecessary!

I think I need to see a lot more information about what your end result is. You want to make your life as simple as possible, not complicate things by entering into countless columns.

I have to leave for the rest of the day, so can't add more now.

Think some more about what you want, and let me know - I should have some time over the weekend.

Regards,

David

Thanks David, I ended up figuring it out. Appreciate the feedback!