时间超过24小时

本文介绍了时间超过24小时的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我设计了一个时间跟踪器数据库......基本上是一个时钟。我有有报告计算每周每周工作的正常工作时数。 目前我正在运行以下查询： -------------------------------------- -------------------------------- SELECT DateFilter.Employee，DateFilter.TimeTypeIn， 总和（[ClockOut] - [ClockIn]）AS Expr1 FROM DateFilter GROUP BY DateFilter.Employee，DateFilter.TimeTypeIn HAVING（（（DateFilter.TimeTypeIn）=" Regular"））; ClockIn& ClockOut都是时间/日期 ------------------------------------ ---------------------------------- 一切都正确计算到24小时。在那之后，它变得混乱了。我希望总和能说出像38:25:02 任何建议都会受到赞赏！

I have designed a "Time Tracker Database"... Basically a Time Clock. I have report that calculates the number of regular hours worked per week. Currently I am running the following query: ---------------------------------------------------------------------- SELECT DateFilter.Employee, DateFilter.TimeTypeIn, Sum([ClockOut]-[ClockIn]) AS Expr1 FROM DateFilter GROUP BY DateFilter.Employee, DateFilter.TimeTypeIn HAVING (((DateFilter.TimeTypeIn)="Regular")); ClockIn & ClockOut are both Time/Date ---------------------------------------------------------------------- Everything calculates correctly up to 24 hours. After that, it messes up. I would like the sum to say something like 38:25:02 Any suggestions would be appreciated!

推荐答案

以您要报告的最小单位进行计算。例如，如果您需要小时和分钟，请在几分钟内完成计算。稍后格式化 获得总数后的方式。不要从ClockOut中减去ClockIn ，而是使用DateDiff函数并告诉它返回分钟（n）。 在你的例子中，看起来你想跟踪到第二个，是 是否正确？ 示例： TotalTime：Sum（Da​​teDiff（" s"，[ ClockIn]，[ClockOut]））\ 3600& 格式（（Sum（Da​​teDiff（" s"，[ClockIn]，[ClockOut]））Mod 3600）/ 86400， "：nn：ss"） 立即窗口中的示例： ？10000 \ 3600&格式（（10000 Mod 3600）/ 86400，"：nn：ss"） 2:46:40 你正在运行的问题进入是你想要的经过时间。过去的 时间不是时间。数据类型，它只是一个数字。例如，下午2点到下午3点之间 的差异是1小时，而不是1点钟（1点）。那么你就好了想要把它格式化好像是时候了。 - Wayne Morgan MS Access MVP " Drum2001" <博士****** @ gmail>在消息中写道 news：11 ********************** @ v46g2000cwv.googlegr oups ... Do the calculation in the smallest units you want to report. For example, if you want hours and minutes, do the calculation in minutes. Format it later the way you want it after you get the total. Instead of subtracting ClockIn from ClockOut, use the DateDiff function and tell it to return minutes (n). In your example, it appears you''re wanting to track to the second, is that correct? Example: TotalTime:Sum(DateDiff("s", [ClockIn], [ClockOut])) \ 3600 & Format((Sum(DateDiff("s", [ClockIn], [ClockOut])) Mod 3600) / 86400, ":nn:ss") Example from the Immediate window: ?10000 \ 3600 & Format((10000 Mod 3600) / 86400, ":nn:ss") 2:46:40 The problem you''re running into is that you want elapsed time. An elapsed time isn''t a "time" data type, it is simply a number. For example, the difference between 2pm and 3pm is 1 hour, not 1 o''clock (1:00). You are then wanting it formatted as if it was a time. -- Wayne Morgan MS Access MVP "Drum2001" <dr******@gmail> wrote in message news:11**********************@v46g2000cwv.googlegr oups... 我设计了一个时间跟踪数据库......基本上是一个时钟。我有报告计算每周工作的正常工作时数。 目前我正在运行以下查询： ---- -------------------------------------------------- ---------------- SELECT DateFilter.Employee，DateFilter.TimeTypeIn， Sum（[ClockOut] - [ClockIn]）AS Expr1 FROM DateFilter GROUP BY DateFilter.Employee，DateFilter.TimeTypeIn HAVING（（（DateFilter.TimeTypeIn）=" Regular"））; ClockIn& ClockOut都是时间/日期 ---------------------------------------- ------------------------------ 一切都能在24小时内正确计算。在那之后，它变得混乱起来。我希望总结能说出像38:25:02 任何建议都会受到赞赏！ I have designed a "Time Tracker Database"... Basically a Time Clock. I have report that calculates the number of regular hours worked per week. Currently I am running the following query: ---------------------------------------------------------------------- SELECT DateFilter.Employee, DateFilter.TimeTypeIn, Sum([ClockOut]-[ClockIn]) AS Expr1 FROM DateFilter GROUP BY DateFilter.Employee, DateFilter.TimeTypeIn HAVING (((DateFilter.TimeTypeIn)="Regular")); ClockIn & ClockOut are both Time/Date ---------------------------------------------------------------------- Everything calculates correctly up to 24 hours. After that, it messes up. I would like the sum to say something like 38:25:02 Any suggestions would be appreciated!

这很有效！谢谢！ 现在这里有点麻烦......我怎么能这样做40以上的时间加价？b $ b小时加班？ 基本上我将如何编码：TotalTime - 40:00:00 =（加班） That works great!! thank you! Now here is a little twist... How would I make it so anything over 40 hours would be overtime? Basically how would I code: TotalTime - 40:00:00 = (overtime)

" Drum2001" <博士****** @ gmail>写在 新闻：11 ********************* @ u72g2000cwu.googlegro ups： "Drum2001" <dr******@gmail> wrote in news:11*********************@u72g2000cwu.googlegro ups: 这很棒!!谢谢！ 现在这里有点麻烦......我怎么能这么做呢？超过40个小时会超时？ 基本上我怎么样？代码：TotalTime - 40:00:00 = （加班） That works great!! thank you! Now here is a little twist... How would I make it so anything over 40 hours would be overtime? Basically how would I code: TotalTime - 40:00:00 = (overtime)

因为以秒为单位的总时间是总和（datediff（" ss"，[clockin]， [clockout]）由Wayne给出， 加班时间为秒数 IIF（总和（datediff（" s，[clockin] ，[clockout]） - 40 * 60 * 60）> 0，总和 （datediff（" s"，[clockin]，[clockout]） - 40 * 60 * 60,0） ） 必须重新格式化，如Wayne所示。 加时赛= IIF（总和（ datediff（s，[clockin]，[clockout]） - 40 * 60 * 60> 0，总和 （datediff（" s"，[clockin]，[clockout]） -40 * 60 * 60,0）/ 3600& 格式（（IIF（sum（datediff（" s"，[clockin]，[clockout]） - 40 * 60 * 60> ; 0， sum（datediff（" s"，[clockin]，[clockout]） - 40 * 60 * 60,0）Mod 3600）/ 86400 ，：nn：ss&q uot;） 必须以单行的形式进入查询，非常麻烦，我确定我是遗漏了一些（）所以请使用下面用户定义的 函数。 超时= SecToDur（IIF（sum（datediff（" s，[clockin] ，[clockout]） - 40 * 60 * 60> 0，sum（datediff（" s"，[clockin]，[clockout]） - 40 * 60 * 60,0）） 公共功能sec2dur（秒长）作为字符串 错误继续下一步 Dim hrs As Long Dim mins As Integer Dim secs as Integer hrs = Int（秒/ 3600） 分钟= Int（（秒 - （3600 *小时））/ 60） 秒=秒 - （小时* 3600 +分钟* 60） sec2dur =格式（小时，＃，## 0"）_ & "：" &安培;格式（分钟，00）& "：" _ &格式（秒，00） 结束功能 - Bob Quintal PA是我改变了我的电子邮件地址。

since the total time in seconds is sum(datediff("ss",[clockin], [clockout]) as given by wayne, Overtime in seconds is IIF(sum(datediff("s",[clockin],[clockout])-40*60*60) >0, sum (datediff("s",[clockin],[clockout])-40*60*60,0)) and that has to be reformatted as Wayne showed, . Overtime = IIF(sum(datediff("s",[clockin],[clockout])-40*60*60 >0, sum (datediff("s",[clockin],[clockout])-40*60*60,0)/3600 & Format((IIF(sum(datediff("s",[clockin],[clockout])-40*60*60 >0, sum(datediff("s",[clockin],[clockout])-40*60*60,0) Mod 3600) / 86400, ":nn:ss") which has to go into the query as a single line, is quite a mess, and I''m sure I''m missing some () so use the User defined function below. overtime = SecToDur(IIF(sum(datediff("s",[clockin],[clockout])- 40*60*60 >0, sum(datediff("s",[clockin],[clockout])-40*60*60,0)) Public Function sec2dur(seconds As Long) As String On Error Resume Next Dim hrs As Long Dim mins As Integer Dim secs As Integer hrs = Int(seconds / 3600) mins = Int((seconds - (3600 * hrs)) / 60) secs = seconds - (hrs * 3600 + mins * 60) sec2dur = Format(hrs, "#,##0") _ & ":" & Format(mins, "00") & ":" _ & Format(secs, "00") End Function -- Bob Quintal PA is y I''ve altered my email address.