Rounding Down - Short Time

[Lewe22]

I have 2 fields in a table called [start_time] and [end_time]. They are both in short time format.

I am displaying these in a form with a calculated field to show the time duration as well. (ie. [duration] = [end_time]-[start_time]) this is also displayed in short time format.

Example

start time      end time      duration
12:00           12:50         00:50
12:00           13:10         01:10
12:00           13:59         01:59
12:00           14:45         02:45

What i now want to show is another column which will round the duration down to the nearest hour and display the figure as a number. So....

start time      end time      duration               duration rounded
12:00           12:50         00:50...would become...0
12:00           13:10         01:10...would become...1
12:00           13:59         01:59...would become...1
12:00           14:45         02:45...would become...2

I have tried a multitude of different ways including ROUND, LEFT, STR etc but have hit the wall. I keep coming unstuck due to the times actually being stored as numbers before they are fomatted into the readable short time.

Hope that made sense. Can anyone help me?

[NeoPa]

As an hour is stored as a twelfth of a day, and a day is a simple integer, you can simply multiply the value by twelve; round; divide the result by twelve.

[youmike]

For twelfth read twentyfourth, so multiply by 24, not 12.

[Lewe22]

Thank you both. I know have it rounding nicely except it will round normally. Any ideas how i would now get it to round DOWN to the nearest whole number?

[Lewe22]

Managed to figure it out using the INT function. Thanks for the suggestions!

=Int([txtDuration]*24)/24

[Lewe22]

On a similar note, do you know how i could reflect the duration column in minutes?

Example

duration               duration (mins)
00:50...would become....50
01:10...would become....70
01:59...would become...119
02:45...would become...165

[NeoPa]

For twelfth read twentyfourth, so multiply by 24, not 12.

Thank you. You're absolutely right of course :)

I had a "Senior Moment".

[NeoPa]

On a similar note, do you know how I could reflect the duration column in minutes?

There are 60 minutes in an hour. Give this a shot yourself. It's really just as easy as you'd expect. No surprises.

[Lewe22]

Oh yeah!!!! Now i feel stupid! Thanks NeoPa

=([txtduration]*24)*60