Converting Seconds into Years,Days, Hours,Minutes, Seconds, input and messagebox vba

thadaniel 0
Viewed 41

I have coded the conversion for hours minutes and seconds already, but when every time I run this code the year changes to 1899 and the days to 30. There must be a small change that could solve this problem but I cant figure out how. I know that vba sets years automatically to 1899 and days to 30 but how can this problem be avoided? Thank you very much!

Option Explicit
Sub Conversion()
Dim inpt As Double
Dim time As Double

inpt = Application.InputBox("Give me your time in seconds: ")

time = inpt / (CDbl(24) * 60 * 60)

MsgBox "Your calculated time is:" & Chr(10) & Year(time) & " years" & Chr(10) & Day(time) & " days" & Chr(10) & Hour(time) & " hours" & Chr(10) & Minute(time) & " minutes" & Chr(10) & Second(time) & " seconds"

End Sub

expected output

vba
2 Answers

What you can do is to do your own math:

Option Explicit

Sub Conversion()
Dim inpt As Double
Dim time As Long

inpt = Application.InputBox("Give me your time in seconds: ")

time = inpt

Dim days As Long: days = Application.WorksheetFunction.Floor(time / (86400), 1)
time = time - (days * 86400)
Dim hours As Integer: hours = Application.WorksheetFunction.Floor((time / 3600), 1)
time = time - (hours * 3600)
Dim minutes  As Integer: minutes = Application.WorksheetFunction.Floor((time / 60), 1)
time = time - (minutes * 60)

MsgBox "Your calculated time is:" & Chr(10) & days & " days" & Chr(10) & hours & " hours" & Chr(10) & minutes & " minutes" & Chr(10) & time & " seconds"

End Sub
Aldert0

As Aldert mention, you need to do your own math.

This one will include years in the calculation and you can adjust some values in the constants to change year from 365 to 366 days for example.

It will not overflow if you enter a big number either.

Option Explicit
Sub Conversion()
    Const YearDays As Long = 365 'Days in a year
    Const DecimalLenght As Long = 12 'Decimals to round in calculation
    Const ScPerDy As Long = 86400 'Seconds in a day
    Const ScPerYr As Long = 31536000 ' Seconds in a year (60 * 365)
    
    Dim inpt As Variant
    
    'Input from user
    inpt = CDec(Application.InputBox("Give me your time in seconds: ", , , , , , , 1))
    
    If inpt = Empty Then
        Exit Sub
    End If
    
    Dim DyConvertFactor As Double
    Dim YrConvert As Double
    Dim TimeFactor As Double
    Dim YrFactor As Double
    Dim DyFactor As Double
    
    DyFactor = inpt / ScPerDy
    YrFactor = inpt / ScPerYr
    
    Dim wsF As WorksheetFunction
    Set wsF = Application.WorksheetFunction
    
    TimeFactor = Round(DyFactor, DecimalLenght) - wsF.RoundDown(DyFactor, 0)
    DyConvertFactor = Round(DyFactor / YearDays, DecimalLenght) - wsF.RoundDown(DyFactor / YearDays, 0)
    
    Dim Sc As Long, Mn As Long, Hr As Long, Dy As Long, Yr As Long
    
    Sc = Second(TimeFactor)
    Mn = Minute(TimeFactor)
    Hr = Hour(TimeFactor)
    Dy = wsF.RoundDown(DyConvertFactor * YearDays, 0)
    Yr = wsF.RoundDown(YrFactor, 0)

    MsgBox "Your calculated time is:" & Chr(10) & _
            Yr & " years" & Chr(10) & _
            Dy & " days" & Chr(10) & _
            Hr & " hours" & Chr(10) & _
            Mn & " minutes" & Chr(10) & _
            Sc & " seconds"
            
End Sub
AlexRivax0
Related