Forum Discussion
MS Project Standard 2021 calculating duration incorrectly
Thanks for all the help!
For anyone that might stumble across this thread in the future wanting to do the same thing (replace specific characters with another font), here's what I finally came up. Tested and works well.
Sub SymbolSubstitution() Dim rng1, rng2, rng3 As Range Dim newText As String Application.ScreenUpdating = False ' Find range to search over. Set rng1 = Cells.Find("*", [a1], xlFormulas, xlPart, xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], xlFormulas, xlPart, xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column)) Else MsgBox "Worksheet is empty", vbExclamation, "Error" Exit Sub End If ' Loop over cells in range. For Each cell In rng3 ' Only check non-empty cells. If cell.Value <> "" Then Call ConvertToSymbolAndReplace(cell, "Δ", "D") Call ConvertToSymbolAndReplace(cell, "Φ", "F") Call ConvertToSymbolAndReplace(cell, "Ω", "W") Call ConvertToSymbolAndReplace(cell, "α", "a") Call ConvertToSymbolAndReplace(cell, "β", "b") Call ConvertToSymbolAndReplace(cell, "χ", "c") Call ConvertToSymbolAndReplace(cell, "δ", "d") Call ConvertToSymbolAndReplace(cell, "ε", "e") Call ConvertToSymbolAndReplace(cell, "η", "h") Call ConvertToSymbolAndReplace(cell, "φ", "j") Call ConvertToSymbolAndReplace(cell, "λ", "l") Call ConvertToSymbolAndReplace(cell, "μ", "m") Call ConvertToSymbolAndReplace(cell, "π", "p") Call ConvertToSymbolAndReplace(cell, "θ", "q") Call ConvertToSymbolAndReplace(cell, "×", "´") Call ConvertToSymbol(cell, "~") Call ConvertToSymbol(cell, "&") Call ConvertToSymbol(cell, "+") Call ConvertToSymbol(cell, "%") Call ConvertToSymbol(cell, "<") Call ConvertToSymbol(cell, "=") Call ConvertToSymbol(cell, ">") Call ConvertToSymbol(cell, "°") Call ConvertToSymbol(cell, "±") Call ConvertToSymbolAndReplaceUnicode(cell, &H2219, &HD7) ' Bullet (must run after × to ´) Call ConvertToSymbolAndReplaceUnicode(cell, &H2211, &H53) ' Sum/sigma sign Call ConvertToSymbolAndReplaceUnicode(cell, &H2212, &H2D) ' Minus sign Call ConvertToSymbolAndReplaceUnicode(cell, &H2264, &HA3) ' Less than or equal sign Call ConvertToSymbolAndReplaceUnicode(cell, &H2265, &HB3) ' Greater than or equal sign Call ConvertToSymbolAndReplaceUnicode(cell, &H221A, &HD6) ' Square root sign Call ConvertToSymbolAndReplaceUnicode(cell, &H221E, &HA5) ' Infinity sign Call ConvertToSymbolAndReplaceUnicode(cell, &H222B, &HF2) ' Integral sign Call ConvertToSymbolAndReplaceUnicode(cell, &H2206, &H44) ' Alternative Delta sign Call ConvertToSymbolAndReplaceUnicode(cell, &H192, &HA6) ' Function sign End If Next cell Application.ScreenUpdating = True End Sub Sub ConvertToSymbolAndReplace(ByRef thisCell As Variant, ByVal inputChar As String, ByVal outputChar As String) Dim charPos As Long charPos = InStr(thisCell.Value, inputChar) Do While charPos > 0 thisCell.Characters(charPos, 1).Text = outputChar thisCell.Characters(charPos, 1).Font.Name = "Symbol" charPos = InStr(charPos + 1, thisCell.Value, inputChar) Loop End Sub Sub ConvertToSymbol(ByRef thisCell As Variant, ByVal inputChar As String) Dim charPos As Long charPos = InStr(thisCell.Value, inputChar) Do While charPos > 0 thisCell.Characters(charPos, 1).Font.Name = "Symbol" charPos = InStr(charPos + 1, thisCell.Value, inputChar) Loop End Sub Sub ConvertToSymbolAndReplaceUnicode(ByRef thisCell As Variant, ByVal inputCharCode As Long, ByVal outputCharCode As Long) Dim charPos As Long charPos = InStr(thisCell.Value, ChrW(inputCharCode)) Do While charPos > 0 thisCell.Characters(charPos, 1).Text = ChrW(outputCharCode) thisCell.Characters(charPos, 1).Font.Name = "Symbol" charPos = InStr(charPos + 1, thisCell.Value, ChrW(inputCharCode)) Loop End Sub
Dale_HowardMVPhere is a screenshot; notice that the durations showing days are correct, but the ones showing weeks are not - where "12 weeks" is shown, it is calculating to 12 days. These are fixed duration tasks, no resources, standard 40 hour workweek.
Hi anjseattle
As Dale pointed out, you most likely have 8 hours in the Hours per week option of your project.
If so, I recommend that you follow the following steps to avoid losing part of your work, if the values shown in the Duration column, at the task level not summary task, are correct:
1. In the tasks table, clear all active filters and expand all summary tasks.
2. Select and Copy the Duration column (to clipboard)
3. Change to 40 hours, the Hours per week option for your project.
4. Paste the Duration column.
5. Put all tasks in Auto Scheduled Task Mode.
N.- The final result of the dates will depend on the different restrictions of the Finish no Earlier Than type, which apparently many of the tasks shown in your screenshot have. I remind you that one of the ways to eliminate them is to leave Type in As Soon As Possible.
I hope you find it useful.
Ignacio Martín
- Thank you! The hours per week worked. I am familiar with the various ways of constraining tasks, but I would have never looked for this.