Consulting

Results 1 to 4 of 4

Thread: calculate 2 datetime and returning to days and hours

  1. #1
    VBAX Newbie
    Joined
    Apr 2014
    Posts
    2
    Location

    calculate 2 datetime and returning to days and hours

    excelforum.com/excel-programming-vba-macros/1007908-calculate-2-datetime-and-returning-to-days-and-hours.html#post3680311

    1st question :
    let see if textbox1 is date and time in, textbox2 is date and time out, textbox3 is calculate the days and hours. problem is the calculation is ok but when i put 25/4/2012 10:00 in textbox1 then textbox2 i put 26/4/2012 9:00 the answer should be 23:00 not 01:00. how to get exactly 23:00


    2st question : in textbox4 should be only the days but i get 0.958333333335759 and suppose it show o and in textbox5 it will show 23 hours


    3rdquestion : in textbox6 and textbox7 is manually put amount to be charge and it will auto calculate at textbox8.


    here i attach my file for better review. sory im still new n still learning


    THANKS IN ADVAND
    Attached Files Attached Files
    Last edited by remie; 04-30-2014 at 11:31 PM. Reason: cross posting

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You need to give your textboxes meaningful names, 1,2,3 is hard to follow.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Newbie
    Joined
    Apr 2014
    Posts
    2
    Location
    let see if :-

    textbox1 = date and time in
    textbox2 = date and time out
    textbox3 = calculate the days and hours


    problem is the calculation is ok but when enter ...


    textbox1 =
    25/4/2012 10:00
    textbox2 = 26/4/2012 9:00
    textbox3 = 01:00

    how to get exactly 23:00 ?
    but if i enter:-

    textbox1 = 25/4/2012 10:00
    textbox2 = 26/4/2012 11:00
    textbox3 = 1 days 01:00 hours (this is correct but if the time enter is below than the time above then the calculation is wrong)



    2st question :
    in textbox4 should be only the days but i get 0.958333333335759 and suppose it show o and in textbox5 it will show 23 hours


    textbox4 = 0.958333333335759 (i want to get how many days but only number not text like textbox3)
    textbox5 = (i want to get the balance hours from textbox3 but just for the hours)

    3rdquestion :

    textbox6 = 40 (manually put amount to be charge)
    textbox7 = 7 (manually put amount to be charge)
    textbox8 = (this will calculate
    textbox6*textbox4 and textbox7*textbox5 ....how to get auto calculation between amount to be charge and the time hours)
    Last edited by remie; 05-01-2014 at 06:17 AM.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    
        If Not IsDate(TextBox1.Value) Then
            
            TextBox1.Value = vbNullString
        Else
        
            TextBox1.Tag = CDate(TextBox1.Value)
            Select Case CDate(TextBox1.Tag)
            Case Is <= 1
                TextBox1.Value = Format(TextBox1.Value, "hh:mm")
            Case Is > 1
                TextBox1.Value = Format(TextBox1.Value, "dd/mm/yyyy hh:mm")
            End Select
            
            If TextBox1.Value <> "" And TextBox2.Value <> "" Then CalculateValues
        End If
    End Sub
    
    Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    
        If Not IsDate(TextBox2.Value) Then
            
            TextBox2.Value = vbNullString
        Else
        
            TextBox2.Tag = CDate(TextBox2.Value)
            Select Case CDate(TextBox2.Tag)
            Case Is <= 1
                TextBox2.Value = Format(TextBox2.Value, "hh:mm")
            Case Is > 1
                TextBox2.Value = Format(TextBox2.Value, "dd/mm/yyyy hh:mm")
            End Select
            
            If TextBox1.Value <> "" And TextBox2.Value <> "" Then CalculateValues
        End If
    End Sub
    
    Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
        If TextBox1.Value <> "" And TextBox2.Value <> "" Then CalculateValues
    End Sub
    
    Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
        If TextBox1.Value <> "" And TextBox2.Value <> "" Then CalculateValues
    End Sub
    
    Private Sub TextBox5_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
        If TextBox1.Value <> "" And TextBox2.Value <> "" Then CalculateValues
    End Sub
    
    Private Sub CalculateValues()
    Dim startDate As Date
    Dim startHours As Double
    Dim endDate As Date
    Dim endHours As Double
    Dim numHours As Double
    Dim numDays As Long
    
        TextBox3.Value = vbNullString
        Select Case True
            Case CDate(TextBox2.Tag) < CDate(TextBox1.Tag)
                TextBox3.Value = "Stop before Start "
                
            Case Is = CDate(TextBox2.Tag) = CDate(TextBox1.Tag)
                TextBox3.Value = "Stop same as Start "
            
            Case Else
                startDate = CDate(TextBox1.Text) \ 1
                startHours = CDate(TextBox1.Text) - startDate
                endDate = CDate(TextBox2.Text) \ 1
                endHours = CDate(TextBox2.Text) - endDate
                numHours = IIf(startHours > endHours, 1, 0) + (endHours - startHours)
                numDays = endDate - startDate - IIf(startHours > endHours, 1, 0)
                If numDays > 0 Then TextBox3.Value = TextBox3.Value & Format(numDays, "0 ""DAY(S)""")
                TextBox3.Value = TextBox3.Value & Format(numHours, " h:mm ""HOURS""")
                TextBox4.Value = numDays
                TextBox5.Value = Format(numHours, "hh:mm ""HOURS""")
        End Select
    End Sub
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •