Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calculate Hours 1

Status
Not open for further replies.

lars7

Technical User
Aug 16, 2005
817
GB
Hi,
How would I calculate the hours between to times ie,

12:00 till 20:00 = 8hrs

Thanks in advance.


Lars7



 



hi,

TIME is part of DATE.

If you take the difference between two Date/Time values you can then convert DAYS to HOURS. 5th grade arithmetic.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Hi Skip,
I've been asked to help with someone elses databse and they don't want to change there current practises so I'm working around what they have.

The time is being recorded as 1200-2000 in a text field called "Time" and them I'm converting the data with this code:

Left([time],InStr([time],"-")-1) --> converts to 12:00 in "Text2"
Mid([time],InStr([time],"-")+1) --> converts to 20;00 in "Text3"


and then I'm appending them to another table inserting them into date fields.

Left([Time2],2) & ":" & Right([Time2],2)
Left([Time3],2) & ":" & Right([Time3],2)

The format of the date fields are short dates and when I try to format as date/time all I get is 12:00:00

I know there is probably an easier way to do this but I'm a bit rusty with access.

thanks for your responce.

 

The time is being recorded as 1200-2000 in a text field called "Time" and them I'm converting the data with this code:
Code:
dim a, t1 as date, t2 as date

a = split([Time],"-")

t1 = TimeSerial(mid(a(0),1,2),mid(a(0),3,2),0)
t2 = TimeSerial(mid(a(1),1,2),mid(a(1),3,2),0)

msgbox "diff is " * abs(t2-t1)*24

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 


oops
Code:
msgbox "diff is " & abs(t2-t1)*24


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Hi Skip,
This looks great but I'm unsure where it's to go.

My current queries runs on the after update of the Time text box and also I was looking for the hours to go into another text box called "Hours"

Thanks for your help.

 
Hi Again,
I tried your code from the after update event and i got a type mismatch error.

Hovering over Abs in debug mode I got

Abs(tl-t2)=8.333333333333333E4-02

thanks.

 


using your example of "1200-2000" in the [TIME] field, the result I get is
[tt]
diff is 8
[/tt]
what value did you use?

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 

Hi Skip,
Sorry I missed your amendment to the code, it works fine but how do I get it to populate the Hours Field?

Thanks,


Lars7
 
Hi Skip,

I got it:


Me.Hours = Abs(t2 - t1) * 24

thanks again

 


You could make it a function that returns the hours
Code:
Me.Hours = HrsDuration([TIME])


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Ok Thanks,

Will have a look at that on Monday.

Lars7
 
Lars7
I'm confused why you didn't provide
lars7 said:
The time is being recorded as 1200-2000 in a text field called "Time"
in the first posting. Clearly anyone attempting to provide any assistance would have made the same assumption as Skip that there were two date/time values.

In the future please provide all the significant information required for someone to not have to guess and make assumptions.

Duane
Hook'D on Access
MS Access MVP
 

...and then
Left([time],InStr([time],"-")-1) --> converts to 12:00 in "Text2"
Mid([time],InStr([time],"-")+1) --> converts to 20;00 in "Text3"
It does not.
You get 1200 and 2000, NOT 12:00 and 20;00

But that's just being picky... :)

Have fun.

---- Andy
 
Hi Duane,
I felt, in the example, I had shown that there was no dates involved but I hope Skip accept my apology for not being clear enough and I will try to be clearer in the future.

Hi Andy,
Yes you are correct; it is the second code example that added the colons.


Hi Skip,
Thanks for the code but unfortunately I can't figure out at this time how to create the function but the after update action is working great and I have added some error handling code for wrongly formatted data which is also working well.

Cheers,

Tom.


 

Code:
function HrsDuration(s as string) as single 
's contains the representations of 2 time values
'  "hhmm-hhmm"
'parse 2 values, convert to TIME, return difference
dim a, t1 as date, t2 as date

a = split(s,"-")

t1 = TimeSerial(mid(a(0),1,2),mid(a(0),3,2),0)
t2 = TimeSerial(mid(a(1),1,2),mid(a(1),3,2),0)

HrsDuration = abs(t2-t1)*24
end function

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Thanks Skip,

I have it working as a function now and hopefully i will understand it one day [bigsmile]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top