How to formula weekly schedule (12 hours per day 8am ~8pm) from cycle time

Hi Vikki,

It makes me happy to see that I have helped you,  Don't hesitate to reach out if you have any questions and you can mark this post as "answered"".  Thank you and have a great year.

cheers

Hey Vikki

Here's one that you don't have to mess with the columns where there is formula in it. you enter the total hours in a separate column:

Play with it and let me know if there are any kinks to be fixed

weekly schedule BTest4.xlsx

Dear Microsoft,

I would like to use Microsoft Excel to create a schedule table.

In team of condition:

  i) 1 day only have 12 hours working time.

 ii) Saturday and Sunday is OFF day.

Each job part number (example: 501-0001) have variance cycle given depend by quantity.

I have some problem to create a excel formula so solve my problem.

Can set to Excel that I want to set my daily time from 24 hours to 12 hours only? Which is everyday only have 12 hours count from  8am to 8pm.

if can't
What is the formula for it (Yellow) in my photo?

Hi Vikki,

Here is the Final version 2: CLICK ME

Image of Solution:

Specifications:

  i) 1 day only have 12 hours working time.

 ii) Saturday and Sunday is OFF day.

Implementation Instructions:

If you want to implement it in your form, you have to include all columns in the solution which means you have to:

1. add a start time cell on top and

2. reformat the date to only show date because the date calculation is separate from the time calculation

3. Keep in mind that the formula in row 4 is different from the formula in row 5, so you cannot copy the formula that is in row 4.

However you can copy the formula from row 5 all the way down to the bottom of your billing form.

4. You can put the table on the right of the worksheet to a different sheet
5. when you're filling up the form like filling out a new part number, you can overwrite the hours billed on the part number and it will still calculate the correct date including Saturdays and Sundays (this is the update for this version).

(make sure you save a template of the solution so you can copy the formulas therein just in case)

I think that is all you have to do.  I hope this solution will help you in your work

Cheers!

I tried to create something for the work orders that are greater than 12 hours, to break it down to 12 hour shifts unfortunately it will only work from 1 hour to 59 hours after that it starts acting stupid, i will share it with you for you to play with.

enter the hours in cell b4 only, for example you have a workorder 501-0001 that has 24 hours enter that number in cell B4

Final version

disclaimer: only for reference and educational purposes 

cheers

Hi

Try the formulas

=$A$23+TIME(C25,0,0)   and copy it down

and

=$A$34+TIME(C36,0,0)   and copy it down

I hope this helps you

Regards

Jeovany