Format for hrs:mins input 3

[rosieb]

I'm working on a time allocation database. Users select a work type then enter the number of hours worked.

At the moment the field is in number format, users have to enter the number in decimal format. I would like to set it up so that they can enter an hh:mm format (the official working day is 7:18).

Can anyone point me in the right direction?

 

[PaulBricker]

Well if they are just entering hours and minutes you can set the field as a date/time and then set the input mask to Short Time. That will do what you want but it won't help convert any values that are input as numbers. You would have to convert those either manually or programatically.

Paul

 

[rosieb]

Thanks
I had a feeling that this wasn't going to be simple. I was hoping for a format that I could SUM easily.

 

[Golom]

Generally, a DateTime data type is not a good choice if you want to do computations (addition / subtraction) on the times. First of all they are limited to 23:59 as their maximum value and second adding 5:27 AM to 7:16 PM (for example) will give you a result but that result is in the next day ... which you are not referencing in your calculations.

I would suggest that you use two text boxes (or whatever) to enter hours and minutes separately and then store a value like (Hours * 60 + Minutes) in the database. Summing it is straight forward and you can convert back to HH:mm with

Hours = Int([MinutesinDB] / 60)
Minutes = [MinutesInDB] Mod 60

 

[PaulBricker]

On this point I will have to disagree a little with Golom (with all due respect). If your field is formatted as Short Time (HH:NN) then you can use this expression

=Int(Sum([TimeField]))*24+DatePart("h",Sum([TimeField])) & ":" & DatePart("n",Sum([TimeField]))

to sum times. You can use it in a report or a query and it will sum values in excess of 23:59. It can also be adapted to sum text datatype values by adding the CDate() function inside the Sum() function (assuming the format is "HH:NN&quot. All date/time datatypes are actually stored as Double Precision values. You can easily manipulate them with most standard functions.

Paul

 

[Golom]

PaulBricker

Thanks for the "... due respect ..." but it's not really necessary since I'm wrong (or at least misguided) as much as I'm right on these forums. I like your approach and it certainly does simplify some of the formatting problems.

 

[rosieb]

Thanks both.

PaulBricker
Particular thanks, that is just what I needed. I wouldn't have got it myself. And I can think of a couple of other places it will come useful.

Much appreciated
Rosie

 

[PaulBricker]

Rosie, glad you got it and glad it my help down the road.

Golom (love that handle) the respect is there because of what you do and how you do it.

Paul

 

[rosieb]

Paul
That's what I really appreciate about this place, the generosity of people like you and Golum, I'd have spent hours without coming up with something so effective - now I've got a template and, once I've really understood it (which I will), I've really learned something.

Rosie

 

[kevinwilson]

I have a similar problem and hope someone here can help. My table consists of two NUMBER fields that represents HOURS and MINUTES, where users input total hrs worked on a project ...in many cases hours worked are in excess of 23:59 hrs/mins. I would like to sum the two fields into a HH:NN (short time) field but of course I'm limited to 23:59. Can this be accomplished in a Short Time field? If so, any assistance would be greatly appreciated. Mahalo.

 

[PaulBricker]

SELECT Sum(tblTime.Hours) AS SumOfHours, Sum(tblTime.Minutes) AS SumOfMinutes, [SumOfHours]+Int([SumOfMinutes]/60) & ":" & [SumOfMinutes] Mod 60 AS MyHoursAndMinutes
FROM tblTime;

This kind of sql will do it.

Paul

 

[kevinwilson]

Gonna give it a try! Mucho mahalo's

kevin

 

[mobmad]

Paul thanks a lot for this - part solved a BIG problem for me creating time sheets in Access from external data.

=Int(Sum([TimeField]))*24+DatePart("h",Sum([TimeField])) & ":" & DatePart("n",Sum([TimeField]))

However when the minutes calc comes to single digit figures then it doesn't display as e.g. 56:04 it displays as 56:4

Does anyone have any ideas on how to get it to display correctly?

I have the property set to Short Time for the calc cell/bound tect box in the report but it still displays without the leading zero for minutes.

Any ideas?

 

[PaulBricker]

You can change the expression like this.

=Int(Sum([TimeField]))*24+DatePart("h",Sum([TimeField])) & ":" & Format(DatePart("n",Sum([TimeField])),"00&quot

Paul

 

[mobmad]

Paul

Many many thanks indeed. Works an absolute treat.

You're a star.

Thanks again.

MOB

 

[georgereni]

to all who can help:

I am exporting a report from our internal reporting server that has information in a time format for each employee.

Example:

LastName, FirstName, Login Time
Smith John 3305:31:40

Which means that John Smith has been logged in for Three Thousand five hours, thirty one minutes and forty seconds.

When I import the data into an access table, it records the time as date and time, and messes the numbers up.

it will show that John Smith's login time as: 4/19/1900 5:00pm, or when I change the setting in the design view to short time it will change the record to 4/19/1900 2:40 am.

How do I get the table to show that the data is hours or total mins calculated and not time?

 

[PaulBricker]

My suggestion would be to import it as Text and then work on the using Access built in functions like CDate() until you get it in the format you want.

Paul

 

[livvie]

PaulBricker I am trying to add hrs and minutes and display them as short time using your code but I am using Access 2003 .adp and I am getting the error 'Int is not a recognised function name ' Any ideas what the syntax for .adp is.

 

[PaulBricker]

livvie, I won't be able to follow up on this till Monday when I get back to work. I don't have any 2003 .adp databases at home but did find some references in my books to User Defined Functions. Apparently, they work better in the newer versions of .adp (newer since 2000 which is what I'm running at home). Two things I found are
1. You can't Sum Date/Time datatypes in SQL Server and
2. The Floor() function works like the Int function in Access.
If your datatype isn't Date/Time, then you can try playing with the Floor() function. Otherwise, I will try and find an answer Monday.

Paul

 

[willir]

livvie

You may want to post your problem as a new thread. I suspect many of us will not re-read a thread that has been resolved, or a specific person is working on the issue.

Also, this forum focuses more on tables and relationships. There is a forum that focuses on VBA, Microsoft: Access Modules (VBA Coding) Forum and another on ADP, Microsoft: Access Project (ADP) Forum

Good luck on getting a resolution.

And Paul, mighty fine stuff!