PDA

View Full Version : calculate 2 datetime and returning to days and hours



remie
04-30-2014, 10:48 PM
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

Bob Phillips
05-01-2014, 04:29 AM
You need to give your textboxes meaningful names, 1,2,3 is hard to follow.

remie
05-01-2014, 05:41 AM
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)

Bob Phillips
05-01-2014, 06:28 AM
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