Covert number into time

[Guest]

Hi

I am trying to analyse an imported appointment database table.
However the time has been entered as 800 instead of 08:00, which is making
it very difficult to evalute.

For example I need to find out the duration period of Arrival time and Seen
time.
Somebody Arrived at 752 and was Seen at 800 the answer should = 8(min)
But the answer comes out at 48, because I have just minused the Seen time
away from the Arrived time.

Does anybody know how I could overcome this?

 

[Jeff Boyce]

Bradley

I don't know if this will help...

Access has a datatype of Date/Time. You can use this to store date and time
information. Access also has a number of functions that work with date/time
data.

If your current design has someone entering 752 to mean 7:52 am on a
particular day, consider revising how you collect that information. One
approach would be to use a form that receives the literal "752", but stores
the date/time equivalent of "today, at 7:52 am". This would make your
date/time arithmetic a lot easier.

 

[Duane Hookom]

To get the minutes, try something like:
60 * Int((Seen -Arrival)/100) + (Seen -Arrival) Mod 60
I have tested this for a few values.

 

[Guest]

Hi Duane

If your saying that your formula correctly calculated 752-800 to equal 8,
then that's exactly what I need.

However, I can't seem to get your formula to work in Access, I get this
message;
Compile error. in query 60*Int(([in]-[time])/100)+([in]-[time]) Mod 60

I tried it in Excel and it fails if I put MOD 60 at the end. I can't even
find the function MOD in Access.

My SQL View of my query is;
SELECT Appointments.slotdate, Appointments.time, Appointments.[in]
FROM Appointments
WHERE (((Appointments.slotdate) Between [Forms]![DateRange]![Text1] And
[Forms]![DateRange]![Text2]) AND ((Appointments.[in])>0));

 

[Duane Hookom]

Are all values in [in] and [time] numbers? Are there any blank values? Are
the data types numeric?

Do you realize that In and Time are both reserved words?

I would create a small function that would convert 852 to 8:52

Function MakeTime(plngTime As Long) As Date
Dim lngMins As Long
lngMins = (plngTime \ 100) * 60
lngMins = lngMins + plngTime Mod 100
MakeTime = lngMins / 1440
End Function

You can then use this function to convert your weird time to an actual time.

--
Duane Hookom
MS Access MVP
--

Hi Duane

If your saying that your formula correctly calculated 752-800 to equal 8,
then that's exactly what I need.

However, I can't seem to get your formula to work in Access, I get this
message;
Compile error. in query 60*Int(([in]-[time])/100)+([in]-[time]) Mod 60

I tried it in Excel and it fails if I put MOD 60 at the end. I can't even
find the function MOD in Access.

My SQL View of my query is;
SELECT Appointments.slotdate, Appointments.time, Appointments.[in]
FROM Appointments
WHERE (((Appointments.slotdate) Between [Forms]![DateRange]![Text1] And
[Forms]![DateRange]![Text2]) AND ((Appointments.[in])>0));

To get the minutes, try something like:
60 * Int((Seen -Arrival)/100) + (Seen -Arrival) Mod 60
I have tested this for a few values.

 

[Ed Warren]

Duane's solution is simple and elegant. I would add, you need to be sure
that the values you have are consistent with regard to using a 24 hour or 12
hour clock.

Duane's solution works if (1:28 pm) is coded as 1328, but if it is coded as
128 you will need to add additional code to handle this case, even worse
would be the case where
1:28 am and 1:28 pm are both coded as 128 in that case you have even more
sorting out to accomplish.

Ed Warren

Are all values in [in] and [time] numbers? Are there any blank values? Are
the data types numeric?

Do you realize that In and Time are both reserved words?

I would create a small function that would convert 852 to 8:52

Function MakeTime(plngTime As Long) As Date
Dim lngMins As Long
lngMins = (plngTime \ 100) * 60
lngMins = lngMins + plngTime Mod 100
MakeTime = lngMins / 1440
End Function

You can then use this function to convert your weird time to an actual
time.

--
Duane Hookom
MS Access MVP
--

Hi Duane

If your saying that your formula correctly calculated 752-800 to equal 8,
then that's exactly what I need.

However, I can't seem to get your formula to work in Access, I get this
message;
Compile error. in query 60*Int(([in]-[time])/100)+([in]-[time]) Mod 60

I tried it in Excel and it fails if I put MOD 60 at the end. I can't even
find the function MOD in Access.

My SQL View of my query is;
SELECT Appointments.slotdate, Appointments.time, Appointments.[in]
FROM Appointments
WHERE (((Appointments.slotdate) Between [Forms]![DateRange]![Text1] And
[Forms]![DateRange]![Text2]) AND ((Appointments.[in])>0));

To get the minutes, try something like:
60 * Int((Seen -Arrival)/100) + (Seen -Arrival) Mod 60
I have tested this for a few values.

--
Duane Hookom
MS Access MVP
--

Hi

I am trying to analyse an imported appointment database table.
However the time has been entered as 800 instead of 08:00, which is
making
it very difficult to evalute.

For example I need to find out the duration period of Arrival time and
Seen
time.
Somebody Arrived at 752 and was Seen at 800 the answer should = 8(min)
But the answer comes out at 48, because I have just minused the Seen
time
away from the Arrived time.

Does anybody know how I could overcome this?

 

[John Marshall, MVP]

Sorry Duane, but I think your formula is out of whack. You should convert to
minutes before doing the subtraction and the mod should be for 100.

60 * Int(Seen/100) + (Seen Mod 100) - 60 * Int(Arrival/100) - (Arrival
Mod 100)

John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm
Visio Wishlist http://www.mvps.org/visio/wish_list.htm

 

[Duane Hookom]

I also realized the expression was riddled with errors so I posted the udf.

 

[John Marshall, MVP]

I noticed your second post after I sent mine. ;-)

John... Visio MVP

 

[Duane Hookom]

John,
I figured... As my two year old grandson would say "I know".
Merry Christmas!