MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Subtracting Times

Posted by Richie Turner on July 23, 2001 3:17 AM

I'm using VBA User form and would like to display the number of Days, Hours, Minutes and seconds to go until a specified date and time in the future.

I'm currently using four seperate calculations for each of the days, hours, mins, secs, using for example:

intMinsToGo = Minute([specified date & time] - Now)

and then joining them together to form a string that displays something like "8d 4h 38m 27s".

This seems to be fine until one of the results is zero. eg. if Specified date and current date falls on the same day, I'll get something like "31d 7h 12m 48s" rather than "0d 7h 12m 48s"

Anyone know how I can get round this or is there a much easier way to get the result I want.

Most grateful for any help


Posted by Jerid on July 23, 2001 2:14 PM

Hi Richie, your problem is that Day return a number from 1 to 31 and when the days are the same (zero days) the function give you the number of days in the month.

Try this code, you should be able to chage it to meet you needs.

Sub ToGo()
Dim iDaysToGo As Integer
Dim iHoursToGo As Integer
Dim iMinsToGo As Integer
Dim iSecsToGo As Integer
Dim vDiff As Variant
Dim dtNow As Date
Dim dtCompare As Date

dtNow = Now()
dtCompare = Range("A1").Value
vDiff = dtCompare - dtNow

If vDiff < 0 Then
MsgBox "Date is in the past"
ElseIf vDiff < 1 Then
iDaysToGo = 0
ElseIf vDiff < 2 Then
iDaysToGo = 1
iDaysToGo = Day(vDiff)
End If

iHoursToGo = Hour(vDiff)
iMinsToGo = Minute(vDiff)
iSecsToGo = Second(vDiff)

MsgBox iDaysToGo & "d " & iHoursToGo & "h " & iMinsToGo & "m " & iSecsToGo & "s "

End Sub