Call number of hours in Project Plan Gantt Chart Template

[GreenLanternX]

Hi Team,

I have project plan which has columns for start date, end date, work days and calendar days.

When working the project you can either enter the estimated number of days as a value, along with the start/end date if known. Using the network days.intl this works out the number of whole days from the data.

The problem is most of our tasks for example only take 1hour, some 0.5 (30mins) some 0.25 (15mins), with some taking 1day or 1.5 days etc.

When I try to enter a value less than 1 (I.e. 0.5) in column L or M, the spreadsheet automatically changes this to the nearest whole number.

How can I amend the formula so that it recognises, mins, hours and days instead of just whole days? See formulas below.

Start date (columnQ):
=IF (OR(J115<>"*, G115<>**),MAX(J115, IF(G115<>**,WORKDAY.INTL(MAXIFERRORINDEX$R$11:$R$126,MATCH(G115, 5B$11: 5B$126, 0)) ,0), IFERRORINDEX$R$11: $R$126, MATCH(H115, $B$11: $B$126, 0)), 0), IFERROR(INDEX$R$11: $R$126, MATCHI115, $B$11: $B$126, 0)),0)),1, weekend, holidays),0)), IF (K115<>** , IF (M115<>**, K115-MAX (®, M115-1), WORKDAY. INTL (K115, - (MAXL115, 1) -1), weekend,holidays)),"-"))

Start date (columnR):
=IF (Q115=* - *,* - *, MAX(K115, IF (M115<>**, 0115+MAX(0, M115-1), WORKDAY. INTL (IF (NETWORKDAYS. INTL (Q115, Q115, weekend,holidays) =0, WORKDAY. INTL (Q115,1,weekend, holidays), 0115), MAX(0, L115-1), weekend, holidays))))

Work days (columnS): = IF (OR (NOT(ISNUMBER (Q115)), NOT(ISNUMBER(R115))),* - *, NETWORKDAYS. INTL (Q115, R115, weekend, holidays))

Calendar days (columnT): = IF (OR(NOT (ISNUMBER(Q115)),NOT(ISNUMBER(R115)))," - ",R115-Q115+1)

Any assistance will be greatly appreciated.

Many thanks

 

[GreenLanternX]

*Re: Calc. number of hours in Project Plan Gantt Chart Template

Hi Team,

I have project plan which has columns for start date, end date, work days and calendar days.

When working the project you can either enter the estimated number of days as a value, along with the start/end date if known. Using the network days.intl this works out the number of whole days from the data.

The problem is most of our tasks for example only take 1hour, some 0.5 (30mins) some 0.25 (15mins), with some taking 1day or 1.5 days etc.

When I try to enter a value less than 1 (I.e. 0.5) in column L or M, the spreadsheet automatically changes this to the nearest whole number.

How can I amend the formula so that it recognises, mins, hours and days instead of just whole days? See formulas below.

Start date (columnQ):
=IF (OR(J115<>"*, G115<>**),MAX(J115, IF(G115<>**,WORKDAY.INTL(MAXIFERRORINDEX$R$11:$R$126,MATCH(G115, 5B$11: 5B$126, 0)) ,0), IFERRORINDEX$R$11: $R$126, MATCH(H115, $B$11: $B$126, 0)), 0), IFERROR(INDEX$R$11: $R$126, MATCHI115, $B$11: $B$126, 0)),0)),1, weekend, holidays),0)), IF (K115<>** , IF (M115<>**, K115-MAX (®, M115-1), WORKDAY. INTL (K115, - (MAXL115, 1) -1), weekend,holidays)),"-"))

Start date (columnR):
=IF (Q115=* - *,* - *, MAX(K115, IF (M115<>**, 0115+MAX(0, M115-1), WORKDAY. INTL (IF (NETWORKDAYS. INTL (Q115, Q115, weekend,holidays) =0, WORKDAY. INTL (Q115,1,weekend, holidays), 0115), MAX(0, L115-1), weekend, holidays))))

Work days (columnS): = IF (OR (NOT(ISNUMBER (Q115)), NOT(ISNUMBER(R115))),* - *, NETWORKDAYS. INTL (Q115, R115, weekend, holidays))

Calendar days (columnT): = IF (OR(NOT (ISNUMBER(Q115)),NOT(ISNUMBER(R115)))," - ",R115-Q115+1)

Any assistance will be greatly appreciated.

Many thanks

*Re: Calc number of hours in Project Plan Gantt Chart Template

 

[GreenLanternX]

*Re: Calc. number of hours in Project Plan Gantt Chart Template



*Re: Calc number of hours in Project Plan Gantt Chart Template

Highlighted grey cells are formulated, pull data values from green highlighted cells.

View attachment 120073

 

[GreenLanternX]

I think the 24 Hour Net Workday Hours Variation may work as a replacement for the workday.intl aspect, as we'd remove the need to calculate ineligible hours, but I'm struggling to recreate a formula that works in place of the above.

 

[GreenLanternX]

I think the 24 Hour Net Workday Hours Variation may work as a replacement for the workday.intl aspect, as we'd remove the need to calculate ineligible hours, but I'm struggling to recreate a formula that works in place of the above.

Example :

-IF Start_Date>End_Date, ©. (NETWORKDAYS(Start_Date, End _Date, HolidayList)

-NETWORKDAYS (Start_ Date, Start Date, HolidayList) *MOD(Start Date, 1)
-NETWORKDAYS (End_Date, End_ Date, HolidayList)*(1-
MOD (End Date, 1) ))*24)