Calculating Level of Effort (LOE) in Excel
Post date: Dec 29, 2015 11:06:23 PM
Calculating a task's LOE between its start and stop dates can be tricky. We have to compensate for weekends, work hours, and holidays. This post takes us through the process step by step.
NOTE! This solution requires XL 2010 or higher because it uses NETWORKDAYS.INTL and Structured References in XL's Tables. To create a table simply enter its column headings, select any heading, press CTRL-T and check My table has headers. To name a table simply select any cell in the table, then in the TABLE TOOLS DESIGN tab, enter the table's name in the Table Name: box on the tab's left.
Setup - Create some meaningful names for our formulas:
Create a table listing your holidays. Name it tblHolidays
Create a table with three columns: Description, Name, and Value. Name it tblConstants
In tblConstants' first row enter: Weekday Start, WDS, 9:00
In tblConstants' second row enter: Weekday End, WDE, 17:00
In tblConstants' third row enter: Weekend Type, WET, 1
In tblConstants select Columns Name:Value and all rows (excluding headers)
Use menu option FORMULAS > Create from Selection. In the dialog box check only Left Column
At this point we have some names we can use in our formula
Setup - Create an entry area for calculating "LOE" (Level of Effort):
Create a table with 6 columns: Start, Stop, TWFD, TWLD, Workdays, LOE.
TWFD means Time Worked First Day
TWLD means Time Worked Last Day
Name the table tblTasks
Enter your start and stop dates in tblTasks
In TWFD column enter this formula:
=MOD(MIN(INT([@Start])+WDE,[@Stop]),1)-MOD([@Start],1)
In English this is: In English this is: The lesser of (Workday's End or Stop time) less Start Time
This gives us the number of hours worked the first day
Use format [h]:mm:s
NOTE: INT(DateTime) provides the date's day portion. MOD(DateTime,1) provides the date's time portion
In TWFD column enter this formula:
=IF(INT([@Stop])=INT([@Start]),0,MOD([@Stop],1) - MOD(MAX(INT([@Stop])+WDS,[@Start]),1))
In English this is: If Start and Stop are on same day, 0, else the Stop time minus the greater of Workday Start and Stop time
This gives us the number of hours worked the last day
Use format [h]:mm:ss
In Workdays column enter this formula:
=NETWORKDAYS.INTL([@Start],[@Stop],WET,tblHolidays)
In English this is: Get the number of working days between start and stop excluding weekends and holidays
See NETWORKDAYS.INT for an explanation of Weekend Types
This gives us the number of workdays including the start and stop dates.
NOTE! This assumes Start and Stop are always during normal working hours.
In the LOE column enter this formula:
=[@TWFD]+[@TWLD]+(MAX(0,[@Workdays]-2)*(WDE-WDS))
In English this is: Add the time worked for first and last days to the number or workdays between start and stop multiplied by the hours between the workday start and workday end.
Use format [h]:mm:ss
In general, project task LOE is calculated in hours and you will want to sum LOEs in hours. But if you want to display LOE in days, add another column with this formula: =INT([@LOE]/(WDE-WDS)) & " days " & TEXT([@LOE]-INT([@LOE]/(WDE-WDS))*(WDE-WDS), "[H]:mm:ss")
Example: https://www.dropbox.com/s/9ytxhvdsixfglaw/LOE.xlsx?dl=1