Calculate final date

Leon445522

New Member
Joined
Jun 10, 2020
Messages
41
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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")
 
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top