Excel that convert SF datetimes to Excel format and vice versa

Public Function ConvertSFDateTime(cel As Range) As Variant

Dim strSFDateTime As String, strSFDatePortion As String, strSFTimePortion As String, strCel As String

Dim sglCharT As Single, sglCharZ As Single, sglTimeLen As Single

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False

strCel = CStr(cel.Value2)

If strCel <> vbNullString Then

 ' +1 added, so that it returns the rightmost part of the string starting at the first character after "T"
 sglCharT = InStr(1, strCel, "T") + 1
 sglCharZ = InStr(1, strCel, "Z")
 sglTimeLen = sglCharZ - sglCharT - 1
 
 strSFDatePortion = Left(strCel, 10)

 strSFTimePortion = Mid(strCel, sglCharT, sglTimeLen)
 
 ConvertSFDateTime = Format(DateValue(strSFDatePortion) + TimeValue(Replace(strSFTimePortion, ".00", "")), _
 "yyyy-mm-dd hh:mm:ss")
 
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True
 
End If

End Function

Public Function ConvertToSFDateTime(cel As Range) As String

Dim strCel As String, strDatePart As String, strTimePart As String

Dim dateCel As Date

Dim sglFractionalLocation As Single

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False

' VBA won't handle fractional seconds, finds the character number in the string where a period occurs
sglFractionalLocation = InStr(1, cel, ".")

' Strips out the fractional second
If sglFractionalLocation > 0 Then
 strCel = Left(cel, sglFractionalLocation - 1)
 Else
 strCel = cel
End If

If IsDate(strCel) Then
 strDatePart = DateValue(strCel)
 ' Determines if time component exists in the value of cel
 If DateValue(strCel) = CDate(strCel) Then
 ConvertToSFDateTime = Format(strCel, "yyyy-mm-dd")
 Else
 ConvertToSFDateTime = Format(strCel, "yyyy-mm-ddThh:mm:ssZ")
 End If
End If

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True

End Function

Public Function ConcatenateMult(rngConcatenateCells As Range, bAddSingleSpace As Boolean, _
Optional strDelimiter As String, Optional strWrapCellValue As String)

Dim rngCel As Range, rngLastCel As Range

Dim bCompareCells As Boolean, bWrapCellValue As Boolean

Dim lngLastRow As Long, lngLastCol As Long

Dim strWrapTrue As String, strWrapFalse As String

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayStatusBar = False

' Sets the bounds for the last row and column from the rngConcatenateCells argument
lngLastRow = rngConcatenateCells.Row + rngConcatenateCells.Rows.Count - 1
lngLastCol = rngConcatenateCells.Column + rngConcatenateCells.Columns.Count - 1

Set rngLastCel = Cells(lngLastRow, lngLastCol)

' Loops through each cell in rngConcatenateCells
' If bAddSingleSpace is True, adds a single space in-between concatenated cells
' If optional strDelimiter argument is provided, adds delimiter between each concatenated value
' If optional strWrapCellValue argument is provided, wraps each concatenated cell with that delmiiter
For Each rngCel In rngConcatenateCells
 bCompareCells = (rngCel.Address = rngLastCel.Address)
 
 ' Sets bWrapCellValue to true and applies strWrapCellValue,
 ' only if the length of the argument is greater than 0 characters
 '
 ' NOTE: [Uses CStr(Replace(rngCel, "'", "\'")) to add the escape "\" character
 ' before all apostrophes, so errors don't occur with SOQL queries
 '
 ' Change this back to CStr(rngCel) if you need to concatenate values without the escape]
 If Len(strWrapCellValue) > 0 Then
 bWrapCellValue = True
 strWrapTrue = strWrapCellValue & CStr(Replace(rngCel, "'", "\'")) & strWrapCellValue
 Else
 bWrapCellValue = False
 strWrapFalse = CStr(Replace(rngCel, "'", "\'"))
 End If

 ' bCompareCells determines if the cell's address is equal to the last cell's address in the range
 Select Case bCompareCells
 ' If true, the strDelimiter and strWrapCellValue arguments are not applied; function ends
 Case Is = True
 If bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapTrue & " "
 If Not bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapFalse & " "
 
 ' If false, continues to concatenate cells
 Case Is = False
 Select Case Len(strDelimiter)
 Case Is = 0
 
 ' Adds a single space in between values
 If bAddSingleSpace Then
 If bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapTrue & " "
 If Not bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapFalse & " "
 Else
 If bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapTrue
 If Not bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapFalse
 End If
 Case Else
 If bAddSingleSpace Then
 If bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapTrue & strDelimiter & " "
 If Not bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapFalse & strDelimiter & " "
 Else
 If bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapTrue & strDelimiter
 If Not bWrapCellValue Then ConcatenateMult = ConcatenateMult & strWrapFalse & strDelimiter
 End If
 End Select
 End Select
Next rngCel

' Hardcoded parentheses added for SFDC conversions -
' Apex Data Loader, Force.com Explorer, and other SOQL query tools require format of values
' in an IN clause as:
'
' ('[value1]', '[value2]', '[value3]',...,'[value(n)]')
'
' (e.g. Select Id, Name FROM Contact WHERE Account.Name IN ('Kimberly-Clark', 'IBM'))
ConcatenateMult = "(" & Trim(ConcatenateMult) & ")"

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayStatusBar = True

End Function