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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,952
Office Version
  1. 2016
Platform
  1. Windows
Try this:
A3 has the project start date and time.
B3 has the project duration.

8 is the number of working hours in a day
Note that the duration must be entered as hours and a proportion of hours so 23 hrs and 50 minutes mustn't be 23:50, but 23.8333. The 23:50 is interpreted as a time and is treated as a number less than 1.

=WORKDAY(A3,B3/8)+8/24+MOD(B3,8)/24

HTH
 
Last edited:

Leon445522

New Member
Joined
Jun 10, 2020
Messages
23
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Try this:
It doesn't take account of weekends.

=INT(A3)+INT(B3/8)+8/24+MOD(B3,8)/24

Thanks Peter,

It is essential for us, that the formula takes into account the work schedule, weekends and holidays.
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,952
Office Version
  1. 2016
Platform
  1. Windows
I thought that after I'd posted, so edited it to take account of weekends. However, this version also takes account of holidays. You just need to list them somewhere and replace the $H$3:$H$5 with the range you have used. A really tidy solution is to name a range as, say, 'holidays' and use that in the formula - then as you add holidays the function will automatically make sure they're included in the calculation.

One other thing, the '8' in '8/24' is the start time rather than the workday duration.

=WORKDAY(A3,B3/8,$H$3:$H$5)+8/24+MOD(B3,8)/24
 

Leon445522

New Member
Joined
Jun 10, 2020
Messages
23
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Those 20 hours must be calculated only during working hours (08:00 - 16:00)
say:
Start prj ........................... No. hours ...................... End Prj
06.03.2020 15:00 .............. 20 ....................... 11.03.2020 11:00
This is the answer ---- 11.03.2020 11:00 ----- if we have holidays on 08.03.2020 and 09.03.2020 (the date is in dd.mm.yyyy format).
On 06.03.2020 15:00 working time for the project is 1 hour (working hours 08:00 - 16:00)
On 07.03.2020 we work 8 hours
on 08.03.2020 holiday
on 09.03.2020 holiday
On March 10, 2020, we work 8 hours
on 11.03.2020, only 3 hours will be worked for the project.
So 1 + 8 + 8 + 3 = 20 hours.
Because the program starts at 8 o'clock and will work only 3 hours on the last day (11.03.2020) until the completion means that the final date will be: 11.03.2020 11:00
That is the date of 11.03.2020 at 11:00.
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,952
Office Version
  1. 2016
Platform
  1. Windows
I got that completely wrong didn't I!! And the problem is not as straightforward as I'd imagined.

Now working on a better solution!! (that works)
 

Leon445522

New Member
Joined
Jun 10, 2020
Messages
23
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Small correction...

Start prj ........................... No. hours ...................... End Prj
06.03.2020 15:00 .............. 20 ....................... 12.03.2020 11:00
This is the answer ---- 12.03.2020 11:00 ----- if we have holidays on 08.03.2020 and 09.03.2020 (the date is in dd.mm.yyyy format).
On 06.03.2020 15:00 working time for the project is 1 hour (from 15:00 - 16:00)
On 07.03.2020 is Saturday
on 08.03.2020 holiday
on 09.03.2020 holiday
On March 10, 2020, we work 8 hours
On March 11, 2020, we work 8 hours
on 12.03.2020, only 3 hours will be worked for the project.
So 1 + 8 + 8 + 3 = 20 hours.
Because the program starts at 8 o'clock and will work only 3 hours on the last day (12.03.2020) until the completion means that the final date will be: 12.03.2020 11:00
That is the date of 12.03.2020 at 11:00.
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,952
Office Version
  1. 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.
 

Amit Tandon

Board Regular
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
EndDateTime_StartDateTime.xlsx
ABCDEFG
11/2/17 2:008:00 AM5:00 PM272:00 PMHolidays
2Start Date / TimeDaily Start TimeDaily End TimeTotal Hours2:30 PM1/5/2017Thu
3MondayLunch Break1/6/2017Fri
4Start - End Time12/23/2016Fri
512/25/2016Sun
612/24/2016Sat
7End Date / Time1/3/2017Tue
81/16/17 9:301/4/2017Wed
9Monday1/7/2017Sat
101/10/2017Tue
111/11/2017Wed
121/13/2017Fri
13
14
15
Sheet1
Cell Formulas
RangeFormula
B1B1=TIME(8,0,0)
C1C1=TIME(17,0,0)
E1E1=TIME(14,0,0)
E2E2=TIME(14,30,0)
A3A3=TEXT(A1,"dddd")
A8A8=MAX(WORKDAY(INT(A1),ROUNDUP(ROUND(D1-MIN((MAX(C1-MAX(MOD(A1,1),B1),0)+MIN((MAX(MOD(A1,1),E1)-E2),0)),D1/24)*(WEEKDAY(A1,2)<=5)*(COUNTIF(F2:F15,INT(A1))=0)*24,6)/ROUND(((C1-B1)-(E2-E1))*24,6),0),F2:F15)+B1+(ROUND(E1-B1,6)<ROUND(MOD((D1-MIN((MAX(C1-MAX(MOD(A1,1),B1),0)+MIN((MAX(MOD(A1,1),E1)-E2),0)),D1/24)*(WEEKDAY(A1,2)<=5)*(COUNTIF(F2:F15,INT(A1))=0)*24)/(((C1-B1)-(E2-E1))*24),1)*((C1-B1)-(E2-E1)),6))*(E2-E1)+MOD((D1-MIN((MAX(C1-MAX(MOD(A1,1),B1),0)+MIN((MAX(MOD(A1,1),E1)-E2),0)),D1/24)*(WEEKDAY(A1,2)<=5)*(COUNTIF(F2:F15,INT(A1))=0)*24)/(((C1-B1)-(E2-E1))*24),1)*((C1-B1)-(E2-E1)),INT(A1)+MAX(MOD(A1,1),B1)+((ROUND(MAX(MOD(A1,1),B1)+D1/24,6)>ROUND(E1,6))*MAX((E2-MAX(E1,MOD(A1,1))),0)*24+D1)/24)
A9A9=TEXT(A8,"dddd")
G2:G12G2=TEXT(F2,"ddd")
 

Amit Tandon

Board Regular
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
EndDateTime_StartDateTime.xlsx
ABCDEFG
11/2/17 2:008:00 AM5:00 PM272:00 PMHolidays
2Start Date / TimeDaily Start TimeDaily End TimeTotal Hours2:30 PM1/5/2017Thu
3MondayLunch Break1/6/2017Fri
4Start - End Time12/23/2016Fri
512/25/2016Sun
612/24/2016Sat
7End Date / Time1/3/2017Tue
81/16/17 9:301/4/2017Wed
9Monday1/7/2017Sat
101/10/2017Tue
111/11/2017Wed
121/13/2017Fri
13
14
15
Sheet1
Cell Formulas
RangeFormula
B1B1=TIME(8,0,0)
C1C1=TIME(17,0,0)
E1E1=TIME(14,0,0)
E2E2=TIME(14,30,0)
A3A3=TEXT(A1,"dddd")
A8A8=MAX(WORKDAY(INT(A1),ROUNDUP(ROUND(D1-MIN((MAX(C1-MAX(MOD(A1,1),B1),0)+MIN((MAX(MOD(A1,1),E1)-E2),0)),D1/24)*(WEEKDAY(A1,2)<=5)*(COUNTIF(F2:F15,INT(A1))=0)*24,6)/ROUND(((C1-B1)-(E2-E1))*24,6),0),F2:F15)+B1+(ROUND(E1-B1,6)<ROUND(MOD((D1-MIN((MAX(C1-MAX(MOD(A1,1),B1),0)+MIN((MAX(MOD(A1,1),E1)-E2),0)),D1/24)*(WEEKDAY(A1,2)<=5)*(COUNTIF(F2:F15,INT(A1))=0)*24)/(((C1-B1)-(E2-E1))*24),1)*((C1-B1)-(E2-E1)),6))*(E2-E1)+MOD((D1-MIN((MAX(C1-MAX(MOD(A1,1),B1),0)+MIN((MAX(MOD(A1,1),E1)-E2),0)),D1/24)*(WEEKDAY(A1,2)<=5)*(COUNTIF(F2:F15,INT(A1))=0)*24)/(((C1-B1)-(E2-E1))*24),1)*((C1-B1)-(E2-E1)),INT(A1)+MAX(MOD(A1,1),B1)+((ROUND(MAX(MOD(A1,1),B1)+D1/24,6)>ROUND(E1,6))*MAX((E2-MAX(E1,MOD(A1,1))),0)*24+D1)/24)
A9A9=TEXT(A8,"dddd")
G2:G12G2=TEXT(F2,"ddd")

Determine End Date / Time (Cell A8) from Start Date / Time (A1) for Total Work Hours Duration (D1) per given: (i) Daily Start & End Time (B1 & C1), (ii) Daily Lunch Start - End Time (E1 & E2) and (iii) Exclude Weekends and Specified Holidays (F2:F15).

Cell A1 has Date & Time Format: "3/4/12 13:30"
Cells B1, C1, E1 & E2 have Time Format: "1:30 PM"
Cells F2:F15 (contain only a date / integer) have Date Format: "3/4/12"
Cell D1 has "General" Format.
Cell A8 (Formula Cell) has Date & Time Format: "3/4/12 13:30"
 

Watch MrExcel Video

Forum statistics

Threads
1,118,792
Messages
5,574,322
Members
412,587
Latest member
Krucial155
Top