• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

calculate 2 datetime and returning to days and hours

R

remie

New Member
calculate 2 datetime and returning to days and hours
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
 

Attachments

In CalculateTextbox3()

Shouldn't the last line:
TextBox3.Value = TextBox3.Value & Format(CDate(TextBox2.Tag) - CDate(TextBox1.Tag) - (Int(DateValue(TextBox2.Tag)) - Int(DateValue(TextBox1.Tag))), "hh:mm") & " HOURS "


Simply be:
TextBox3.Value = Format(CDate(TextBox2.Tag) - CDate(TextBox1.Tag), "hh:mm") & " HOURS "
 
http://www.vbaexpress.com/forum/show...029#post309029
http://www.ozgrid.com/forum/showthre...871#post711871

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)

THANKS IN ADVAND
 
Option Explicit

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Application.EnableEvents = False
If Not IsDate(TextBox1.Value) Then
TextBox1.Value = vbNullString
GoTo End_Sub
End If
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 = "" Then GoTo End_Sub
If TextBox2.Value = "" Then GoTo End_Sub
CalculateTextBox3
CalculateTextBox4
End_Sub:
Application.EnableEvents = True
End Sub

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Application.EnableEvents = False
If Not IsDate(TextBox2.Value) Then
TextBox2.Value = vbNullString
GoTo End_Sub
End If
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 = "" Then GoTo End_Sub
If TextBox2.Value = "" Then GoTo End_Sub
CalculateTextBox3
CalculateTextBox4
End_Sub:
Application.EnableEvents = True
End Sub

Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Value = "" Then Exit Sub
If TextBox2.Value = "" Then Exit Sub
CalculateTextBox3
End Sub

Private Sub CalculateTextBox3()
TextBox3.Value = vbNullString
Select Case CDate(TextBox2.Tag) - CDate(TextBox1.Tag)
Case Is > 1
TextBox3.Value = DateValue(TextBox2.Tag) - DateValue(TextBox1.Tag) & " DAYS "
Case Is = 1
TextBox3.Value = "1 DAYS "
Case Is < 0
TextBox3.Value = "Stop before Start "
GoTo End_Sub
Case Is = 0
TextBox3.Value = "Stop same as Start "
GoTo End_Sub
End Select
TextBox3.Value = TextBox3.Value & Format(CDate(TextBox2.Tag) - CDate(TextBox1.Tag) - (Int(DateValue(TextBox2.Tag)) - Int(DateValue(TextBox1.Tag))), "hh:mm") & " HOURS "

End_Sub:

Application.EnableEvents = True
End Sub
Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Value = "" Then Exit Sub
If TextBox2.Value = "" Then Exit Sub
CalculateTextBox4
End Sub

Private Sub CalculateTextBox4()
TextBox4.Value = vbNullString
Select Case CDate(TextBox2.Tag) - CDate(TextBox1.Tag)
Case Is > 1
TextBox4.Value = DateValue(TextBox2.Tag) - DateValue(TextBox1.Tag)
Case Is = 1
TextBox4.Value = ""
Case Is < 0
TextBox4.Value = "Stop before Start "
GoTo End_Sub
Case Is = 0
TextBox4.Value = "Stop same as Start "
GoTo End_Sub
End Select
TextBox4.Value = TextBox4.Value & Format(CDate(TextBox2.Tag) - CDate(TextBox1.Tag), "")

End_Sub:

Application.EnableEvents = True
End Sub

Private Sub UserForm_Click()

End Sub
 
thanks mr hui i get the answer but that for calculation in textbox5. i already change your format at textbox5.

Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Value = "" Then Exit Sub
If TextBox2.Value = "" Then Exit Sub
CalculateTextBox4
End Sub

Private Sub CalculateTextBox4()
TextBox4.Value = vbNullString
Select Case CDate(TextBox2.Tag) - CDate(TextBox1.Tag)
Case Is > 1
TextBox4.Value = DateValue(TextBox2.Tag) - DateValue(TextBox1.Tag)
Case Is = 1
TextBox4.Value = ""
Case Is < 0
TextBox4.Value = "Stop before Start "
GoTo End_Sub
Case Is = 0
TextBox4.Value = "Stop same as Start "
GoTo End_Sub
End Select
TextBox4.Value = Format(CDate(TextBox2.Tag) - CDate(TextBox1.Tag), "hh")
now i want to get this answer but days. how many days calculation in textbox1 between texbox2

End_Sub:

Application.EnableEvents = True
End Sub
 
i already have the answer from forum http://www.vbaexpress.com/forum/sho...turning-to-days-and-hours&p=309015#post309015 now my problem is how to

calculate 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)
i want to calculate textbox6*textbox4 and textbox7*textbox5 and the answer will be at textbox8
 
You must log in or register to reply here.
Back
Top