How to write time in coding

mattmcclements

New Member
Joined
Apr 15, 2022
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
This is a silly question but how do you write time in terms of vba email coding. My spreadsheet generates an automatic email and in the body it will detail the how many minutes late someone was. But how do you get it to write 2 minutes and not 1.38888888888888E-03 minutes? This is the current code.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MailAddress As String
Dim MailAddress_CC As String
Dim ByDate As String
Dim ByDate1 As String
Dim ByDate2 As String
Dim ByDate3 As String
On Error Resume Next

If Intersect(Range("K2:K1000"), Target) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

If IsNumeric(Target.Value) Then

If Target.Value >= 3 Then

MailAddress = Range("M" & Target.Row).Value

ByDate = (Cells(Target.Row, "A"))

ByDate1 = (Cells(Target.Row, "C"))

ByDate2 = (Cells(Target.Row, "G"))

ByDate3 = (Cells(Target.Row, "K"))

Call Mail_small_Text_Outlook(MailAddress, MailAddress_CC, ByDate, ByDate1, ByDate2, ByDate3)

' Send the email
emailItem.Send


End If

End If

End Sub
Sub Mail_small_Text_Outlook(MailAddress As String, MailAddress_CC As String, ByDate As String, ByDate1 As String, ByDate2 As String, ByDate3 As String)
Dim xOutApp As Object
Dim xOutMail As Object
Dim cell As Range
Dim strbody As String

Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)

On Error Resume Next
With xOutMail
.To = MailAddress
.Cc = ""
.BCC = ""
.Subject = "Driver Errors Investigation"
.htmlBody = "Hi," & vbNewLine & vbNewLine & "you have a lates investigation to complete on " & ByDate & " who was late on " & ByDate1 & " by " & ByDate2 & " minutes, this is their " & ByDate3 & " offence."
.Display
End With

On Error GoTo 0

Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub
Sub Check_Offences()

Dim MailAddress As String
Dim MailAddress_CC As String
Dim ByDate As String
DimLr As Long
Dim r As Long
On Error Resume Next


With Sheets("Lates")

'Get last row

Lr = .Range("A" & .Rows.Count).End(xlUp).Row
For Each cell In .Range("J2:J" & Lr)

If IsNumeric(cell) And cell >= 3 And Not cell.Offset(0, 2) = "Y" Then '<<< Assumes 'Email Sent' Confirmation Y' ' in column L <<< ???(Offset 2)
r = cell.Row
MailAddress = .Range("M" & r).Value
MailAddress_CC = .Range("" & r).Value

'String of Date in C '??
ByDate = CDate(.Range("C" & r))

Call Mail_small_Text_Outlook(MailAddress, MailAddress_CC, ByDate) '<<<<<<

' Send the email
emailItem.Send


'Mark email as Sent in column L <<<< ?????
.Range("L" & r) = "Y"


Else
End If



Any help would be greatly appreciated :)
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
I have not tried to replicate your setup & test your current code, but instead of ..
VBA Code:
 " by " & ByDate2 & " minutes
.. try code like
VBA Code:
" by " & Format(ByDate2, "n") & " minutes
 
Last edited:
Upvote 0
Solution
Thank you Peter, how would that be written in terms of the rest of the code as it is coming up with a syntax error. I'm still new to vba so this is probably an easy fix but when I change the rest of the ByDate2's in the code to Format(ByDate2, "n") it doesn't work.

Thank you again
 
Upvote 0
Cannot tell the problem without more information.
Could you give the revised code that you have and a row of dummy sample data with XL2BB after hiding the irrelevant columns?
 
Upvote 0
Cannot tell the problem without more information.
Could you give the revised code that you have and a row of dummy sample data with XL2BB after hiding the irrelevant columns?
Apologies it was an error on my half. Everything is working now, thank you so much!
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top