auto calculate end date

[mmh21]

Hi,

In my production sheet, I would like auto calculate end date and time based on start date/time, total required hours, excluding weekend and holiday, and based on working hours

Machine AllocatedHours start date End Date Date Day Working hours
Machine 1 30.25 17/07/2021 10:35 ?? 17/07/2021 Sat 7
Machine 2 30.25 18/07/2021 14:00 18/07/2021 Sun 7
Machine 3 30.25 19/07/2021 09:00 19/07/2021 Mon 7
Machine 4 30.25 20/07/2021 14:00 20/07/2021 Tue 7
Machine 5 30.25 21/07/2021 14:00 21/07/2021 Wed 0
Machine 6 37.25 22/07/2021 13:00 22/07/2021 Thu 0
Machine 7 40.25 23/07/2021 00:00 23/07/2021 Fri 0
Machine 8 30.25 24/07/2021 12:00 24/07/2021 Sat 7

Thanks
mmh

[alansidman]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[mmh21]

Hi Alan,
Thank You! Please find herewith attach sample file

[alansidman]

A couple of questions.
You have indicated no work on weekends, yet your start date is scheduled for some Saturdays and Sundays. Please clarify
In the last column, you have 7 or 0. Please explain. Does this represent hours already spent? Normal Workday? If normal workday, explain how to handle 0 hours.

[mmh21]

Hi,
In my country friday is weekend and sat/Sunday is normal working day. So 0(zero) indicates no working day. The last column indicates working hours for those dates. So based on working hours, i would like to find auto calculate end date and time. 2nd column indicates allocated hours to finish job for the machine.

[tanasedn]

Hi,
The machine work only 7 hours/day? Or is working continuously? As I see you have different times for machine job starting. There is no connection in your database between machine job start and working hours.

[mmh21]

Hi,
There is just sample data. The machine would be operated/run based on column G (working hours and its basically 7 hours) in a day.

[tanasedn]

In this case, I need a start working our for all working days. Even there are sample data, the situation need to be close to reality. The machine start to work in the working hour interval. What is that interval? We need this information for an accurate calculation.

[mmh21]

HI,
Thank you! Please consider the Job instead of Machine. In the Data ist column (A) Job category, 2nd column(B)required hours to finish this job, 3rd Column(C)Start Date with time of this job.
I would like to auto calculate the finishing Date and time of the Job, based on working hours( Column G) and total required hours (Column B) to finish this job. Here Job start 8 am and end time 3 pm (total 7 hours) in the normal working day. In Column G (working Hours) Friday and holiday indicates 0 working hours (no workind day) , and 7 normal working hours.
Here Date Column(E) just for reference to find working day and holiday.

Thanks and Regards
MMH

[josephteh]

With Start Hour at cell G3, End Hour at cell G4 and Working Hours at cell G5, and Holidays in a table named "Holidays",

End Date: =WORKDAY.INTL(C3+1,ROUNDUP((B3-($G$4-MOD(C3,1))*24)/$G$5,0),"0000100",Holidays)+MOD(B3-($G$4-MOD(C3,1))*24,$G$5)/24+$G$3

[mmh21]

Hi Joshep,
Thank You! It's a great help for me. It works!

[josephteh]

Sorry, there is an error, the correct formula should be: =WORKDAY.INTL(C3,INT((B3-($G$4-MOD(C3,1))*24)/$G$5)+1,"0000100",Holidays4)