Calculate final date

[Leon445522]

Howdy,

How can I calculate the day of completion of a project, if I have the date and time of starting the project and the number of hours assigned to that project.
The working day starts at 08:00 and ends at 16:00.
Weekends and holidays will be excluded here.

ex:
Start project ................. number hours ....... End project
10/06/2020 08:30 ........ 34 ............................. ??/??/???? ??:??

The data is in dd/mm/yyyy format

 

[Leon445522]

Hi,

Hopefully this now works correctly. I've used named ranges throughout as I think its easier to read. The formula is:

=WORKDAY(St_Date,INT((Duration+(St_Date-INT(St_Date))-Start_Time)/E2),tblHolidays[Holidays])+Start_Time+MOD((Duration+(St_Date-INT(St_Date))-Start_Time),Working_Hours)

where:

St_Date is the project start date and time.
Start_Time is the start of the working day
tblHolidays[Holidays] is a single column table named tblHolidays with its column called Holidays. The data is simply the dates of when holidays occur. Saturday and Sunday are assumed to be non-working days.
Working_Hours is the difference between End_Time and Start_Time. Assuming you enter the Start_Time and End_Time as proper time values (a proportion of 24 hours) then this will also be a proportion of 24 hours and will be correct.
Duration is the duration of the project in hours and minutes entered in normal Excel time format. Note, its worth having a custom format of [hh]:mm as this allows durations of over 24hours to be entered correctly.

Hope this helps.

Hi,

Thanks for the reply.
What is E2 in your formula?

 

[Leon445522]

@Amit Tandon,

Can you change your formula to exclude Lunch Break?

 

[Amit Tandon]

@Amit Tandon,

Can you change your formula to exclude Lunch Break?

To exclude Lunch Break you may enter the same time ie. 2.00 pm for both Start-End Times of Lunch Break or else just delete both Start-End Times of Lunch Break.

You may download the excel file from this link wherein it has been illustrated - Click here.

 

[pjmorris]

Hi,

Thanks for the reply.
What is E2 in your formula?

E2 should have said Working_Hours

The correct formula is here:

=WORKDAY([@[St_Date]],INT(([@Duration]+([@[St_Date]]-INT([@[St_Date]]))-Start_Time)/Working_Hours),tblHolidays[Holidays])+Start+MOD(([@Duration]+([@[St_Date]]-INT([@[St_Date]]))-Start_Time),Working_Hours)

I used a spreadsheet layout like this to test the operation. It acts like a simple PMP, but can only deal with single activity predecessors.
For info, you're holiday table doesn't need to list Sundays as they are assumed by WORKDAY to be non working - if this might be incorrect then there is an alternative function that can cater for custom weekends.

Project Management Calculator.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Project Start	15:00 Fri 06-Mar-20
2								Working Time
3	Activity Name	Duration	Predecessor(s)	St_Date	Finish Date/Time			Name	Time		Holidays
4	Initiate	04:00		15:00 Fri 06-Mar-20	11:00 Tue 10-Mar-20			Start_Time	08:00		Sun 08-Mar-20
5	Next Task	02:00	4	11:00 Tue 10-Mar-20	13:00 Tue 10-Mar-20			End_Time	16:00		Mon 09-Mar-20
6	Second Task	07:00		15:00 Fri 06-Mar-20	14:00 Tue 10-Mar-20			Working_Hours	08:00
7	Third Task	01:00	5	13:00 Tue 10-Mar-20	14:00 Tue 10-Mar-20
Sheet1

Cell Formulas
Range	Formula
D4:D7	D4	=IF([@[Predecessor(s)]]<>"",INDIRECT("E" & [@[Predecessor(s)]]),$B$1)
E4:E7	E4	=WORKDAY([@[St_Date]],INT(([@Duration]+([@[St_Date]]-INT([@[St_Date]]))-Start_Time)/Working_Hours),tblHolidays[Holidays])+Start_Time+MOD(([@Duration]+([@[St_Date]]-INT([@[St_Date]]))-Start_Time),Working_Hours)
I6	I6	=End_Time-Start_Time

Named Ranges
Name	Refers To	Cells
Duration	=Sheet1!$I$6	E4:E7
End_Time	=Sheet1!$I$5	I6
Start_Time	=Sheet1!$I$4	I6, E4:E7
Working_Hours	=Sheet1!$I$6	E4:E7

HTH

 

[Leon445522]

@pjmorris ,

Please, can you upload your file here (dropbox.....)
Can't make it work.
Thank you so much.

 

[pjmorris]

Hi Leon,

This should do it: Project Management Calculator.xlsx

Let me know how you get on.

All the best.