Calculate Hours 1

[lars7]

Hi,
How would I calculate the hours between to times ie,

12:00 till 20:00 = 8hrs

Thanks in advance.


Lars7

 

[SkipVought]

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]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lars7]

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.

 

[SkipVought]

The time is being recorded as 1200-2000 in a text field called "Time" and them I'm converting the data with this 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]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

oops

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

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

 

[lars7]

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.

 

[lars7]

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.

 

[SkipVought]

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]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lars7]

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

 

[lars7]

Hi Skip,

I got it:


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

thanks again

 

[SkipVought]

You could make it a function that returns the hours

Me.Hours = HrsDuration([TIME])

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

 

[lars7]

Ok Thanks,

Will have a look at that on Monday.

Lars7

 

[dhookom]

Lars7
I'm confused why you didn't provide

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

 

[Andrzejek]

...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

 

[lars7]

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.

 

[SkipVought]

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]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lars7]

Thanks Skip,

I have it working as a function now and hopefully i will understand it one day