Calculate final date

Leon445522

New Member
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

New Member
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,

What is E2 in your formula?

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Leon445522

New Member
@Amit Tandon,

Can you change your formula to exclude Lunch Break?

Amit Tandon

Board Regular
@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.

pjmorris

Well-known Member
Hi,

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
ABCDEFGHIJK
1Project Start 15:00 Fri 06-Mar-20
2Working Time
3Activity NameDurationPredecessor(s)St_DateFinish Date/TimeNameTimeHolidays
4Initiate04:0015:00 Fri 06-Mar-2011:00 Tue 10-Mar-20Start_Time08:00Sun 08-Mar-20
5Next Task02:00411:00 Tue 10-Mar-2013:00 Tue 10-Mar-20End_Time16:00Mon 09-Mar-20
6Second Task07:0015:00 Fri 06-Mar-2014:00 Tue 10-Mar-20Working_Hours08:00
7Third Task01:00513:00 Tue 10-Mar-2014:00 Tue 10-Mar-20
Sheet1
Cell Formulas
RangeFormula
D4:D7D4=IF([@[Predecessor(s)]]<>"",INDIRECT("E" & [@[Predecessor(s)]]),\$B\$1)
E4:E7E4=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)
I6I6=End_Time-Start_Time
Named Ranges
NameRefers ToCells
Duration=Sheet1!\$I\$6E4:E7
End_Time=Sheet1!\$I\$5I6
Start_Time=Sheet1!\$I\$4I6, E4:E7
Working_Hours=Sheet1!\$I\$6E4:E7

HTH

Leon445522

New Member
@pjmorris ,

Can't make it work.
Thank you so much.

Replies
4
Views
261
Replies
4
Views
212
Replies
0
Views
158
Replies
7
Views
154
Replies
3
Views
156