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:

• 1. Create a table listing your holidays. Name it tblHolidays

  2. Create a table with three columns: Description, Name, and Value. Name it tblConstants

  3. In tblConstants' first row enter: Weekday Start, WDS, 9:00

  4. In tblConstants' second row enter: Weekday End, WDE, 17:00

  5. In tblConstants' third row enter: Weekend Type, WET, 1

  6. In tblConstants select Columns Name:Value and all rows (excluding headers)

  7. 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):

• 1. Create a table with 6 columns: Start, Stop, TWFD, TWLD, Workdays, LOE.

  2. TWFD means Time Worked First Day

  3. TWLD means Time Worked Last Day

  4. Name the table tblTasks

  5. Enter your start and stop dates in tblTasks

  6. In TWFD column enter this formula:

  7. =MOD(MIN(INT([@Start])+WDE,[@Stop]),1)-MOD([@Start],1)

  8. In English this is: In English this is: The lesser of (Workday's End or Stop time) less Start Time

  9. This gives us the number of hours worked the first day

  10. Use format [h]:mm:s

  11. NOTE: INT(DateTime) provides the date's day portion. MOD(DateTime,1) provides the date's time portion

  12. In TWFD column enter this formula:

  13. =IF(INT([@Stop])=INT([@Start]),0,MOD([@Stop],1) - MOD(MAX(INT([@Stop])+WDS,[@Start]),1))

  14. 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

  15. This gives us the number of hours worked the last day

  16. Use format [h]:mm:ss

  17. In Workdays column enter this formula:

  18. =NETWORKDAYS.INTL([@Start],[@Stop],WET,tblHolidays)

  19. In English this is: Get the number of working days between start and stop excluding weekends and holidays

  20. See NETWORKDAYS.INT for an explanation of Weekend Types

  21. This gives us the number of workdays including the start and stop dates.

  22. NOTE! This assumes Start and Stop are always during normal working hours.

  23. In the LOE column enter this formula:

  24. =[@TWFD]+[@TWLD]+(MAX(0,[@Workdays]-2)*(WDE-WDS))

  25. 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.

  26. 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