1. Registered User
Join Date
May 2006
Posts
178

Hey guys,

I have 5 txt boxes on a form

txtSDate (this is for Start Date)
txtSTime (this is for Start time in HHMM format)
txtFDate (this is for Finish Date)
txtFTime (this is for Finish Time)
txtTimeworked (I want this showing in hours and minutes)

Please can someone help me in this calculation as im lost with regards to the syntax.

Regards

Aboo

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
Time is hard cal out

as you want hours and minutes

1st let find how mins there are then div that by 60 which give us hours find out what let that must be the mins

Dim txtStartTime as Date
Dim txtEndTime as Date
Dim TotalMins as long

txtStartTime = txtSDate & " " & txtSTime
txtEndTime = txtFDate & " " & txtFTime

TotalMins = DateDiff("n",txtStartTime,txtEndTime)

THours = int(TotalMins / 60) ' Number of hours only want the hole hours INT() drop every thing after the .
TempHour = THours * 60

so the differance between the THours and TempHour will be less the 60 there must be the Mins left over.

TotalMins = THour - Temphour

txtTimeworked = THour & ":" & TotalMins

So that it now leet make a function to do the job

Code:
```Function TotMins(Stime As Date, Etime As Date)
Dim TotalMins As Long
TotalMins = DateDiff("n", Stime, Etime)
THours = Int(TotalMins / 60)  ' Number of hours
Temphour = THours * 60
TotalMins = TotalMins - Temphour
TotMins = THours & ":" & TotalMins
End Function```

if you want 01:09 format

TotMins = format(THours,"00") & ":" & format(TotalMins,"00")

now now to get it working

open a module and paste the code into close the module save it

then in the form you can just call it

txtStartTime = txtSDate & " " & txtSTime
txtEndTime = txtFDate & " " & txtFTime

txtTimeworked = TotMins(txtStartTime, txtEndTime)

or

txtTimeworked = TotMins(txtSDate & " " & txtSTime, txtFDate & " " & txtFTime)

trun the txtTimeworked into a unbound box and '= TotMins(txtSDate & " " & txtSTime, txtFDate & " " & txtFTime)' in the control source.

aboo a test for you can you change the code so its work out the days also....

ie 25:30 = 1:01:30
Last edited by myle; 02-15-09 at 21:33.

3. Registered User
Join Date
May 2006
Posts
178
Myle,

Thankyou for your reply . I will try this today. thanks again!....

I will take up the challenge of the test.... remember though... your gonna pay for the asprin after i develop a headache!..lol

4. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
I wonder if I can claim back for asprin on expenses?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
the problem I see here is how do you work out the elapsed time
are you looking for true elapsed time or elapsed working time.

true elapsed time is relatively trivial
make both the start and end date a date time value
then do a datediff().. that will give you the elapsed time in wahtever units you want.
essentially this is Myles suggestion. personally I'd want to factor in a bit more error checking to make sure the proposed values are valid dates and times. mind you if it was me I'd only be displaying date and time as separate values if I had to.. far better to use the inherent date/time datatype.

however if you want elapsed working time then you need to know start/finish times per day, non working days.
you calculate the number of full working days (you probably need a working calendar). it gets more complicated if you allow part working days (eg say Saturday's)
the amount of time between the event start and the close of business that day.
the amount of time between the opening of business that day and the event end time.

6. Registered User
Join Date
May 2006
Posts
178
Hey guys thanks for your replies.

Well essentially its is as follows.

A user will input a start date and a start time then a finish date and a finish Time.

So here is an example.

Start date: 17/02/09 Start time: 23:00hrs

Finish date: 18/02/09 Finish time: 06:00hrs

i want the result to obviously show 7 hours.

You guys see what i mean?

Thanks

Aboo

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
so myles suggestion should meet your requirements

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
Originally Posted by aboo
Myle,

Thankyou for your reply . I will try this today. thanks again!....

I will take up the challenge of the test.... remember though... your gonna pay for the asprin after i develop a headache!..lol

And yess you will get a headache

and the asprin will come when you see it working and test a

01/01/09 23:00 to 02/05/09 14:45 and it returns the right answer.

LOL

9. Registered User
Join Date
May 2006
Posts
178
Myle,

Did I pass?

Code:
```Function TotMins(Stime As Date, Etime As Date)
Dim TotalMins As Long
TotalMins = DateDiff("n", Stime, Etime)
Days = Int(TotalMins / 1440)  ' Number of days
TempDays = Days * 1440
TotalMins = TotalMins - TempDays 'Number of hours left over after deducting the days(in minutes)
Thours = Int(TotalMins / 60)  ' Number of hours
Temphour = Thours * 60
TotalMins = TotalMins - Temphour
'TotMins = THours & ":" & TotalMins
TotMins = Days & ":" & Format(Thours, "00") & ":" & Format(TotalMins, "00")

End Function```

Woot Woot!

Did I pass?

Took me over an hour.......lol........

almost forgot the answer is it........120:15:45

Aboo

Join Date
Feb 2004
Location
New Zealand
Posts
1,482