How do i display differences between dates using 2 datediff results?

[brendanmcdonagh]

Hi,

Does anyone know how to display differences between 2 times from 2 different dtpicker displaying results in HH:mm rather than just h or n? I am designing a project for friend so she can enter her start, end, and lunch time in order to calculate total weekly hours worked.

I did have a text box input way of doing until i realised that half an hour was 0.70! Any way of rescuing that way of doing it sounds good.



E.g, I have 15:00 and 17:15, I want label1.caption to display 2hrs 15mins.

Apart from the obvious attempts I have tried the following but the answer for 15:00 17:00 is 2.25

Dim minutesdiv, total1, time1hour, time2hour, total2, time1minute, time2minute, timediff As Variant


Private Sub Command1_Click()
time1hour = DTPicker1.Hour
time1minute = DTPicker1.Minute
time2hour = DTPicker2.Hour
time2minute = DTPicker2.Minute
total1 = time1hour & "." & time1minute
total2 = time2hour & "." & time2minute
timediff = DateDiff("n", total1, total2)
minutesdiv = timediff / 60
Label1.Caption = minutesdiv

End Sub

I have used variant as I am new to Vb, I would change appropriatly if I can get working.

I genuinely have spent about 12 hours on this small issue.

[lotus18]

Hi,

Does anyone know how to display differences between 2 times from 2 different dtpicker displaying results in HH:mm rather than just h or n? I am designing a project for friend so she can enter her start, end, and lunch time in order to calculate total weekly hours worked.

I did have a text box input way of doing until i realised that half an hour was 0.70! Any way of rescuing that way of doing it sounds good.


E.g, I have 15:00 and 17:15, I want label1.caption to display 2hrs 15mins.

Apart from the obvious attempts I have tried the following but the answer for 15:00 17:00 is 2.25

Dim minutesdiv, total1, time1hour, time2hour, total2, time1minute, time2minute, timediff As Variant

Private Sub Command1_Click()
time1hour = DTPicker1.Hour
time1minute = DTPicker1.Minute
time2hour = DTPicker2.Hour
time2minute = DTPicker2.Minute
total1 = time1hour & "." & time1minute
total2 = time2hour & "." & time2minute
timediff = DateDiff("n", total1, total2)
minutesdiv = timediff / 60
Label1.Caption = minutesdiv
 
End Sub
 
 

I have used variant as I am new to Vb, I would change appropriatly if I can get working.

I genuinely have spent about 12 hours on this small issue.

Hi brendanmcdonagh

Try this:

Private Sub Command1_Click()
     Label1.Caption = Format((DTPicker1.Value - DTPicker2.Value), "hh:nn")
End Sub
 

Try to add a validation (e.g. Time1 must higher than Time2) Just continue it...

Hope this helps : )

Rey Sean

[brendanmcdonagh]

You don't know how relieved I am that it works.

I can go to sleep now!

Thank you so much,

Brendan

[brendanmcdonagh]

Hi again,

The code given above works fine unless my friend started at 23:00 and end at say 17:00, it's getting confused I know. So does that mean I need to have custome dtpicker showing date as well as time? How would custom that if I need to?

It really seems to be getting complicated because when I get the answer to the above code, then I'm going to have to add them all up, add lunch amounts in time format and then subtract total of lunch times as well. Primary testing shows again it'll get confused.

Surely People have done this kind of thing before on vb, all I Want to do is add start time, end time, how much for lunch for each day of week and then calculate amount of hours worked.

Any direction would be greatly received (I want to do it my self, I just need a nudge!)

[CyberSoftHari]

Nothing complicated, Use DateDiff and pass date and time to get working hours will solve your problem.

[brendanmcdonagh]

Hi CyberSoftHari

The reason i'm trying simple subtraction is I've tried datediff, calculations are coming up wrong for some reason. For instance, a simple code with start being 01:00 and end being 09:15 is giving me the answer (a headache!) 8.25. Now I'm not that good at maths but shouldn't it be 8.15?

Dim time As Double
Dim timediv As Double
Private Sub Command1_Click()
time = DateDiff("n", DTPicker1.Value, DTPicker2.Value)
timediv = time / 60
Label1.Caption = timediv

End Sub

[QVeen72]

Hi,

Change your Code to :

Dim time1 As Double
Private Sub Command1_Click()
time1 = DateDiff("n", DTPicker1.Value, DTPicker2.Value)
Label1.Caption = (time1 \ 60)  & ":" & (time1 Mod 60)
End Sub
 
' Or In Single Statement:
 
Label1.Caption = (DateDiff("n",DTPicker1.Value, DTPicker2.Value) \ 60) & ":" & (DateDiff("n",DTPicker1.Value, DTPicker2.Value) Mod 60)
 
 

Dont Use "Time" as Varaible Name as It is a Reserved Word in VB

Regards
Veena

[brendanmcdonagh]

Hi,

Change your Code to :

Dim time1 As Double
Private Sub Command1_Click()
time1 = DateDiff("n", DTPicker1.Value, DTPicker2.Value)
Label1.Caption = (time1 \ 60)  & ":" & (time1 Mod 60)
End Sub
 
' Or In Single Statement:
 
Label1.Caption = (DateDiff("n",DTPicker1.Value, DTPicker2.Value) \ 60) & ":" & (DateDiff("n",DTPicker1.Value, DTPicker2.Value) Mod 60)
 
 

Dont Use "Time" as Varaible Name as It is a Reserved Word in VB

Regards
Veena

Thanks Veena (again!)

I have done the following code instead which shows the same answer -

Lblsunsubtotal.Caption = Format((DTPickersunend.Value - DTPickersunstart.Value), "hh:nn")

lblmonsubtotal.Caption = Format((DTPickermonend.Value - DTPickermonstart.Value), "hh:nn")

Just the same - right?

Any ideas how to add the answers together?

[QVeen72]

Hi,

Uee the Same Logic :

 
lblTotal = 
Format((DTPickersunend.Value - DTPickersunstart.Value + DTPickermonend.Value - DTPickermonstart.Value), "hh:nn")
 
 

Regards
Veena

[brendanmcdonagh]

Thank you so much - I hope I can give back to community one day soon!

[brendanmcdonagh]

Veena (or anyone!)

I thought just adding another subtraction for lunch in the calculation would be simple.

What im doing: Taking end time away from start time and then taking away lunch time then I 'm adding result to next calculation for mondays, then tuesday, etc.

The answer is coming up for start at 07:00, end at 19:00 and lunch is 01:00 is 13:00. Itseems to be adding lunch instead of taking it away.

If I can solve this I've created my first program! (with a little help!) ;)

lbltotal = Format((DTPickersunend.Value - DTPickersunstart.Value - DTPickersunlunch.Value + DTPickermonend.Value - DTPickermonstart.Value - DTPickermonlunch.Value + DTPickertuesend.Value - DTPickertuesstart.Value - DTPickertueslunch + DTPickerwedsend.Value - DTPickerwedsstart.Value - DTPickerwedslunch.Value + DTPickerthursend.Value - DTPickerthursstart.Value - DTPickerthurslunch.Value + DTPickerfriend.Value - DTPickerfristart.Value - DTPickerfrilunch.Value + DTPickersatend.Value - DTPickersatstart.Value - DTPickersatlunch.Value), "hh:nn")

[QVeen72]

Hi,

Your Lunch "13:00" is the Time of Lunch..
But What is the Duration of Lunch..?
As you dont have LunchStart And LunchEnd, you Simply
cannot Subtract Lunch.DatePicker Value..

If Duration is Fixed everyday, say 1 Hr/30 mins, Directly Subtract it....

Regards
Veena

[brendanmcdonagh]

Lunch won't be fixed everyday, So is my only option to have dtpicker for lunch start and end and then somehow enter them into calculation? No chance of having text box input for lunch to take away?

[VACEPROGRAMER]

Hi friend

I have a that program in my book

when i get home i will send you

Thanks for the help

Vace

[brendanmcdonagh]

Hi friend

I have a that program in my book

when i get home i will send you

Thanks for the help

Vace

Thanks Vace, I appreciate it :)

[VACEPROGRAMER]

Dont wory
Lately I will send you tomorow

Dont wory my FRINED

By the way Thanks for the help

Do you want to get in my team a?

[QVeen72]

Lunch won't be fixed everyday, So is my only option to have dtpicker for lunch start and end and then somehow enter them into calculation?

Hi,

Yes, Thats better Have one More DatePicker for LunchStart and LunchEnd,
Then WorkHours PerDay will be:

DayEnd - DayStart -(LunchEnd - LunchStart)

Regards
Veena

[brendanmcdonagh]

Thanks Veena,

Any idea how i can stop it resetting at 24 hours. e.g. 25:00 is how i want it to be displayed but it'll display currently 01:00. It's to do with hh:nn custom format ------- .Value + DTPickerfriend.Value - DTPickerfristart.Value - DTPickerfrilunch.Value + DTPickersatend.Value - DTPickersatstart.Value - DTPickersatlunch.Value), "hh:nn")

I have asked a few questions this weekend but have learnt so much!

[QVeen72]

Hi,

For time Formats, you cannot make Entry like "25:00"..
What you can do is :
Format DatPicker for Both Date and Time
Or
Use TextBox, with proper Validations.(Coding required)

First Option will be Good.

Regards
Veena

[VACEPROGRAMER]

Sorry I'm late. I have found the program you want and I make somewhone with same function. I'll send you abaout 2 or 3 hours

Vace