JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,484
- Office Version
- 365
- Platform
- Windows
I have had it with the Convert function. The units choices are illogical and inconsistent. The latest version at least provides a list of the units, but I still have to scroll down to find the one I want.
Until m$ft really fixes it, (yeah, right), I'm going to use my MyConvert UDF. It just handles days and hours, but I'll add to it as I need more.
And here's a test table:
Comments?
PS: Fixed a typo
Until m$ft really fixes it, (yeah, right), I'm going to use my MyConvert UDF. It just handles days and hours, but I'll add to it as I need more.
VBA Code:
'======================================================================================
' My Convert Function
' The built in Convert function uses weird, inconsistent codes for the units.
' MyConvert will take rational ones and call Convert with the ones it wants.
' It uses the UnitsBad2Good subfunction so I have just one place to add units.
' 09/26/21 Created
'======================================================================================
Function MyConvert(pValue As Variant, pUnitsOld As String, pUnitsNew As String) As Variant
Dim UnitsOld As String
Dim UnitsNew As String
UnitsOld = UnitsWeird2Normal(pUnitsOld)
UnitsNew = UnitsWeird2Normal(pUnitsNew)
MyConvert = Application.WorksheetFunction.Convert(pValue, UnitsOld, UnitsNew)
End Function
' This function handles the units conversion
Function UnitsWeird2Normal(pUnitsBad As String) As String
Select Case UCase(pUnitsBad)
Case "DAYS": UnitsWeird2Normal = "day"
Case "HOURS": UnitsWeird2Normal = "hr"
End Select
End Function
And here's a test table:
Fun Facts.xlsx | ||||||
---|---|---|---|---|---|---|
C | D | E | F | |||
6 | Start | Stop | Convert | MyConvert | ||
7 | 01/01/00 07:00 | 01/01/00 07:00 | 0.0000 Hours | 0.0000 Hours | ||
8 | 01/01/00 07:00 | 01/01/00 07:15 | 0.2500 Hours | 0.2500 Hours | ||
9 | 01/01/00 07:00 | 01/01/00 09:30 | 2.5000 Hours | 2.5000 Hours | ||
10 | 01/01/00 07:00 | 01/01/00 11:47 | 4.7833 Hours | 4.7833 Hours | ||
11 | 01/01/00 07:00 | 01/01/00 11:47 | 4.7833 Hours | 4.7833 Hours | ||
12 | 01/01/00 07:00 | 01/02/00 23:03 | 40.0500 Hours | 40.0500 Hours | ||
temp |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E7:E12 | E7 | =CONVERT(D7-C7,"day","hr") |
F7:F12 | F7 | =myCONVERT(D7-C7,"days","hours") |
Comments?
PS: Fixed a typo