Calculate final date

Leon445522

New Member
Joined
Jun 10, 2020
Messages
23
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Jun 10, 2020
Messages
23
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?
 

Some videos you may like

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.

Amit Tandon

Board Regular
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
@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

Well-known Member
Joined
Aug 2, 2012
Messages
1,952
Office Version
  1. 2016
Platform
  1. Windows
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
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
Joined
Jun 10, 2020
Messages
23
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
@pjmorris ,

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

Watch MrExcel Video

Forum statistics

Threads
1,118,814
Messages
5,574,486
Members
412,597
Latest member
Timtec
Top