I need Time field greater than 23:59 (hh:mm). Any help Please. 5

[Modex]

Hi All,

Got a little problemette here and I can’t seem to get around it, I was hoping someone may have an idea.

I have written a work recording database which keeps record of work that the 3 of us technicians do during the day. One of the fields on the form is the amount of time taken to do the job and the field is set as a “medium time” format DATE field and works fine as long as the job takes no more than 23:59 hours/minutes to do, however there are big activities we do that last 200+ hours that need to be recorded. The time fields are added up at the end of the month so the time format really needs to be in a time format.

So basically I need a field where I can put either dd:hh:mm or just hh:mm but the hh part of the field can be greater than 23.

I just can’t seem to find a way to do it, any help would be very gratefully received

Many thanks

ModeX

 

[MakeItSo]

I assume these values are accumulated (added) values. Why don't you just split that in two fields (hrs_workd, mins_workd).
Then you could transform it into ddhhmm format with
days_wrkd=hrs_wrkd\24
hrs_wrkd=hrs_wrkd mod 24

Would that solve your problem?

Cheers,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[MichaelRed]

MS does not provide a format of date/time which can display HOURS greater than 23. All Ms. Date/time values are stored as a floating point (Double) value with the "Integer" portion representing hte number of DAYS (24 Hour units) from 21/39/1898 and the "decimal" portion representing the "part" (percentage) of a single day. When the part increases to "1.", it (obviously?) rolls over to the integer part and becomes a FULL day and the decimal portion resets back to zero.

A 'task' which takes more than one "session" should be recorded on a persession basis and the individual sessions should be accumulated to reflect the total. Basically, you have not considered normaliziation of your data. If you enter the session start and stop times as full date/time values, you can manipulate them to arrive at the session duration. Adding the session durations (grouped by task) provides the necessary aggregate time sepnt on the task. Display of this can be accomplished with the CInt([TotalTime]) as days and the remainder as Format([Totalime], "ShortTime&quot.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[sedgely]

try formatting the cell as [h]:mm:ss from the custom format list

Cheers, Craig
Si fractum non sit, noli id reficere

 

[Sabrina224]

Why not just use a single number field where hours are integers and minutes are decimals on a 100 base system. That way, you don't need separate fields for hours and minutes, and you can sum them up to your hearts delight (i.e., 2.25=2hrs 15min, 2.5=2hr 30min, 2.75=2hr 45min) I use this at work where we round everything to the nearest 15 minute intervals. And if you really need to be more specific (and with 200+ hr projects, you probably would), you could create a simple lookup table to convert each minute into the 100 base system. (All you're really doing here is dividing the minutes by 60, so 30 minutes = .5).

 

[MichaelRed]

Sabrina224 ,

You should "look into" the Ms. Date/Time representation (very BRIEFLY) described above. You appear to be doing some excess "work" in your own apps and providing un-necessary advice in htis matter (there are 86400 Seconds in a day).





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[TonyJollans]

Hi Modex,

As Michael says, there is no format (builtin or custom) to do what you want. You should, however, use Int (which truncates) rather than CInt (which rounds) to get days out of a date.

To produce the string (and it can only be a string) you want to see, you must do some sort of calculation. Exactly what rather depends on exactly what you want to do. If you only ever want to be able to input values less than 24 hours then I would suggest you use date/time fields for your input. Totals of multiple input values can be calculated (and they will be correct) and totals of calculated sub-totals can also be made - your only problem, as I think you already realise, is getting them to appear the way you want. If you make your totals (and sub-totals) invisible date/time values, and add extra (visible) textboxes for each, containing the formula ..

=Int(CalculatedDateTimeValue*24) & ":" & Format(CalculatedDateTimeValue,"nn&quot

.. you can make it all look as I think you want.

It gets much more complicated if you actually want to input values greater than 24 hours because you will have to fully interpret input strings yourself.

Michael,

When exactly was 21/39/1898? I think I must have slept through it.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Modex]

Thanks Guys,

Theres lots of suggestions here and on getting back to work on monday I'll try them out.

Thanks once again for all the replies.

Its very much appreciated

ModeX

 

[Sabrina224]

Michael,
Please help me to be better at what I do and let me know why my method would be considered extra "work" as you put it. I designed a simple table in excel w/3 columns. 1st with the date/time format which Modex is looking for so the user can select for the amount of time worked on a given day as 13:42 (if they happened to work 13 hrs and 42 minutes on that project that day; 2nd the # of minutes col 1 equates to; 3rd the 100 base system -- copied and pasted formulas as values, and imported into access. (The value stored to the project table would be col2 (if you wanted total minutes and then calculate that to dd:hh:mm format) or col 3 (if you wanted the total to be 57.5 [57 hours and 30 min]). This took all of 7 minutes and the table size is just over 100kb and yes, has 1439 rows. (I would agree that using seconds would be ludicrous, and I would not have mentioned using this method if Modex had noted that he needed the time to the nearest second). Granted the table size could be reduced to 89kb as col2 is not necessary with col3 and visa-versa, but as it's on the one side of a one-to-many, 20kb isn't too much to get stressed about.

While I do not profess that this is the best solution, it is a solution that will do exactly what Modex described and it can be implemented in less time than I'm sure you devoted to your answer and certainly less time than I'm devoting to this response. I am also puzzled at your comment about my providing "un-necessary advice" on this subject. You may not like my solution, and while I can appreciate your opinion, (and am looking forward to trying your solution to removing holidays from date calculations - my code has some flaws I can't seem to get around) if you continue to tell people who post possible solutions to their problems that their advice is unnecessary, you are defeating the purpose of free-thinking however "un-necessary" you may find it to be.

Best of luck!

 

[MichaelRed]

Sabrina224,

use the ubiquitous {F1} (a.k.a Help topic: DateDiff. In particular, see the Units for the difference ("s&quot. This CAN privide the appropiate "Interval" in Seconds. The following returns something like the format requested. NO table, Columns, No Records.

Public Function basSec2Day(lngSecIn As Long) As String

    'Michael Red    11/11/2003 Tek-Tips thread703-695284 for Arryb

    Const SecPerDay As Long = 86400
    Dim MySecs As Long
    Dim Idx As Integer

    MySecs = lngSecIn

    While MySecs > SecPerDay

        MySecs = MySecs - SecPerDay
        Idx = Idx + 1

    Wend

     basSec2Day = Str(Idx) & " Days and " & Format(MySecs / 86400, "h:m:s")

End Function
[code]

Of course, this 'assumes' that they will also accept the previous suggestion and enter the individual session times, not the "Total Time" for a task.  Since I can't quite believe that anyone would (reliably) keep track of their "session" times over the course of several weeks, add them up properly and then enter such values, the change to the data base structure to includde start and end times for eack 'session' worked on a task seems much more reasonable.

I did not mean to be condescending in my post to you, and apologise for appearing so.  Of course we shouuld all be able to see other approaches and learn "better" (more efficient / pratical) methods of solving problems in every part of our lives, but probably more so in places like Tek-Tips.  The major reason I am a member is specifically to do that (learn).  It just [i][u]seems[u][i] (to me) that the soloution available with a small UDF and intrinsic functions whould be much 'better' (more efficient?) than an additional table and the function to do the look-up (conversion).




MichaelRed
m.red@att.net

Searching for employment in all the wrong places