Rounding Hours (Date/Time Field)

[tbl]

I'd like to be able to round times to the neares hour, where
any value within 13 minutes before, and 13 minutes after,
[TimeFieldValue] would return the hour between those two
extremes. For example 09:55 would return 10:00.

This would be for queries that feed reports, where a task it
supposed to happen "on the hour", but it's fine if the work
was done a few minutes before or after the hour. I just
want the report to reflect the "on the hour" time.

 

[Gary Walter]

I'd like to be able to round times to the neares hour, where
any value within 13 minutes before, and 13 minutes after,
[TimeFieldValue] would return the hour between those two
extremes. For example 09:55 would return 10:00.

This would be for queries that feed reports, where a task it
supposed to happen "on the hour", but it's fine if the work
was done a few minutes before or after the hour. I just
want the report to reflect the "on the hour" time.
--

I'm not sure this is exactly what you want, but I will try
to break it down so maybe you can adapt..

A typical "round" on a float goes like

Int(x * Factor + 0.5) / Factor

the "*Factor" moves the decimal point so many places
until the "round" placeholder is immediately on right of
decimal point, i.e.,

x = 0.245

want to round to nearest tenth --> factor = 10

x*10 = 2.45

then, add .5 to handle whether "round up or down"

x*10 + .5 = 2.96

the Int() then chops off everything to right of decimal point

Int(x*10 + .5) = 2.00

then, multiply by factor to move decimal point back

Int(x*10 + .5)/10 = 0.200

So...

in the case of time, left of decimal point measures
fraction of a day (or of 24 hours)

so, 24 * (thistimefraction) gives hours

so, if you want to round to nearest hour

CDate(Int(tm*(24)+0.5)/(24))

should work to round "tm" field to nearest hour

 

[Guest]

Use your field instead of [Date open] in the statement below.

On-time or Early/Late: IIf((DateDiff("n",0,[Date open]-Int([Date open])))
Mod 60>=47 Or (DateDiff("n",0,[Date open]-Int([Date open]))) Mod 60<=13,"On
time","Early/Late")

 

[Gary Walter]

just to be clear

CDate(Int(tm*(24)+0.5)/(24))

did not really have a "Factor" per se

(It was like we wanted to round

34.36 to 34.00
or
34.51 to 35.00

Int(x + .5)

--> there is no need to "move the decimal"
because we are rounding to nearest unit,
just like in our "time rounding" we were
rounding to nearest hour)


but, if wanted, the "factor" for time
will be determined a little differently..

-- to round to the nearest half hour,
there are two half-hour-sects in an hour,
so the "factor" is 2

CDate(Int(tm*(24)*(2)+0.5)/(24)*(2))

-- to round to the nearest 10 minutes,
there are six 10-min-sects in an hour,
so the "factor" is 6

CDate(Int(tm*(24)*(6)+0.5)/(24)*(6))

I'd like to be able to round times to the neares hour, where
any value within 13 minutes before, and 13 minutes after,
[TimeFieldValue] would return the hour between those two
extremes. For example 09:55 would return 10:00.

This would be for queries that feed reports, where a task it
supposed to happen "on the hour", but it's fine if the work
was done a few minutes before or after the hour. I just
want the report to reflect the "on the hour" time.
--

I'm not sure this is exactly what you want, but I will try
to break it down so maybe you can adapt..

A typical "round" on a float goes like

Int(x * Factor + 0.5) / Factor

the "*Factor" moves the decimal point so many places
until the "round" placeholder is immediately on right of
decimal point, i.e.,

x = 0.245

want to round to nearest tenth --> factor = 10

x*10 = 2.45

then, add .5 to handle whether "round up or down"

x*10 + .5 = 2.96

the Int() then chops off everything to right of decimal point

Int(x*10 + .5) = 2.00

then, multiply by factor to move decimal point back

Int(x*10 + .5)/10 = 0.200

So...

in the case of time, left of decimal point measures
fraction of a day (or of 24 hours)

so, 24 * (thistimefraction) gives hours

so, if you want to round to nearest hour

CDate(Int(tm*(24)+0.5)/(24))

should work to round "tm" field to nearest hour

 

[Jamie Collins]

I'd like to be able to round times to the neares hour, where
any value within 13 minutes before, and 13 minutes after,
[TimeFieldValue] would return the hour between those two
extremes. For example 09:55 would return 10:00.

This would be for queries that feed reports, where a task it
supposed to happen "on the hour", but it's fine if the work
was done a few minutes before or after the hour. I just
want the report to reflect the "on the hour" time.

First some test data:

CREATE TABLE Test (TimeFieldValue DATETIME NOT NULL)
;
INSERT INTO Test (TimeFieldValue) VALUES (#2007-01-01 09:46:59#)
;
INSERT INTO Test (TimeFieldValue) VALUES (#2007-01-01 09:47:00#)
;
INSERT INTO Test (TimeFieldValue) VALUES (#2007-01-01 09:55:00#)
;
INSERT INTO Test (TimeFieldValue) VALUES (#2007-01-01 10:05:00#)
;
INSERT INTO Test (TimeFieldValue) VALUES (#2007-01-01 10:13:00#)
;
INSERT INTO Test (TimeFieldValue) VALUES (#2007-01-01 10:13:01#)
;

The following SQL is for demonstration purposes only: I wouldn't
normally split up SQL like this, rather I'd do it in one, but this
shows the logic better):

SELECT TimeFieldValue,
DATEADD('H', INT((DATEDIFF('S', #1990-01-01 00:00:00#,
TimeFieldValue) / 60 / 60) + 0.5), #1990-01-01 00:00:00#) AS
nearest_hour,
IIF(ABS(DATEDIFF('S', nearest_hour, TimeFieldValue)) <= 13 * 60,
nearest_hour, NULL) AS result
FROM Test
;

 

[tbl]

just to be clear

CDate(Int(tm*(24)+0.5)/(24))

did not really have a "Factor" per se

(It was like we wanted to round

34.36 to 34.00
or
34.51 to 35.00

Int(x + .5)

--> there is no need to "move the decimal"
because we are rounding to nearest unit,
just like in our "time rounding" we were
rounding to nearest hour)


but, if wanted, the "factor" for time
will be determined a little differently..

-- to round to the nearest half hour,
there are two half-hour-sects in an hour,
so the "factor" is 2

CDate(Int(tm*(24)*(2)+0.5)/(24)*(2))

-- to round to the nearest 10 minutes,
there are six 10-min-sects in an hour,
so the "factor" is 6

CDate(Int(tm*(24)*(6)+0.5)/(24)*(6))

I'd like to be able to round times to the neares hour, where
any value within 13 minutes before, and 13 minutes after,
[TimeFieldValue] would return the hour between those two
extremes. For example 09:55 would return 10:00.

This would be for queries that feed reports, where a task it
supposed to happen "on the hour", but it's fine if the work
was done a few minutes before or after the hour. I just
want the report to reflect the "on the hour" time.
--

I'm not sure this is exactly what you want, but I will try
to break it down so maybe you can adapt..

A typical "round" on a float goes like

Int(x * Factor + 0.5) / Factor

the "*Factor" moves the decimal point so many places
until the "round" placeholder is immediately on right of
decimal point, i.e.,

x = 0.245

want to round to nearest tenth --> factor = 10

x*10 = 2.45

then, add .5 to handle whether "round up or down"

x*10 + .5 = 2.96

the Int() then chops off everything to right of decimal point

Int(x*10 + .5) = 2.00

then, multiply by factor to move decimal point back

Int(x*10 + .5)/10 = 0.200

So...

in the case of time, left of decimal point measures
fraction of a day (or of 24 hours)

so, 24 * (thistimefraction) gives hours

so, if you want to round to nearest hour

CDate(Int(tm*(24)+0.5)/(24))

should work to round "tm" field to nearest hour

Thanks for the replies, Gary. I'll give it a go when the
fires subside...

 

[tbl]

Use your field instead of [Date open] in the statement below.

On-time or Early/Late: IIf((DateDiff("n",0,[Date open]-Int([Date open])))
Mod 60>=47 Or (DateDiff("n",0,[Date open]-Int([Date open]))) Mod 60<=13,"On
time","Early/Late")

Thanks, Karl. I've been side-tracked, but will get to this
again soon.

 

[tbl]

I'd like to be able to round times to the neares hour, where
any value within 13 minutes before, and 13 minutes after,
[TimeFieldValue] would return the hour between those two
extremes. For example 09:55 would return 10:00.

This would be for queries that feed reports, where a task it
supposed to happen "on the hour", but it's fine if the work
was done a few minutes before or after the hour. I just
want the report to reflect the "on the hour" time.

First some test data:

CREATE TABLE Test (TimeFieldValue DATETIME NOT NULL)
;
INSERT INTO Test (TimeFieldValue) VALUES (#2007-01-01 09:46:59#)
;
INSERT INTO Test (TimeFieldValue) VALUES (#2007-01-01 09:47:00#)
;
INSERT INTO Test (TimeFieldValue) VALUES (#2007-01-01 09:55:00#)
;
INSERT INTO Test (TimeFieldValue) VALUES (#2007-01-01 10:05:00#)
;
INSERT INTO Test (TimeFieldValue) VALUES (#2007-01-01 10:13:00#)
;
INSERT INTO Test (TimeFieldValue) VALUES (#2007-01-01 10:13:01#)
;

The following SQL is for demonstration purposes only: I wouldn't
normally split up SQL like this, rather I'd do it in one, but this
shows the logic better):

SELECT TimeFieldValue,
DATEADD('H', INT((DATEDIFF('S', #1990-01-01 00:00:00#,
TimeFieldValue) / 60 / 60) + 0.5), #1990-01-01 00:00:00#) AS
nearest_hour,
IIF(ABS(DATEDIFF('S', nearest_hour, TimeFieldValue)) <= 13 * 60,
nearest_hour, NULL) AS result
FROM Test
;

Many thanks, Jamie. I'll get back to this soon.

 

[tbl]

On Tue, 6 Mar 2007 17:37:40 -0600, "Gary Walter"


Thanks for the reply, Gary.

[snip]

So...

in the case of time, left of decimal point measures
fraction of a day (or of 24 hours)

Wouldn't that be *right* of the decimal point?

 

[Gary Walter]

Of course...

As a former computer tech, the situation
came up so many times that when I asked
a user to right-mouse click on the desktop
(and nothing happened), I started saying

"Please click on the *other* right-mouse button.."


"tbl"wrote:

On Tue, 6 Mar 2007 17:37:40 -0600, "Gary Walter"


Thanks for the reply, Gary.

[snip]

So...

in the case of time, left of decimal point measures
fraction of a day (or of 24 hours)

Wouldn't that be *right* of the decimal point?

 

[tbl]

Of course...

As a former computer tech, the situation
came up so many times that when I asked
a user to right-mouse click on the desktop
(and nothing happened), I started saying

"Please click on the *other* right-mouse button.."

ROFL!