时间戳

首页 > 解决方案 > 我应该如何在 VBA 中以分钟为单位获得时间

excel - 我应该如何在 VBA 中以分钟为单位获得时间

问题描述

我在 VBA 中存储大量数据，然后在 excel 中导出该文件。如果时间大于一小时，我可以收集时间，但如果小于 59 分钟，我无法收集时间。我正在使用类似的函数来计算时差

  1. // 获取时间差 00:00
  2. var time = TIMEDIFF('00:00', $inspection_time);
  3. // 比如检查时间是12:00pm，结果应该是0.5
  4. 变率=时间/24；
  5. SETRESULT(率);

标签： excelms-accessvba

解决方案

First off, TIMEDIFF is a function I did not immediately find documentation for.

Also, time/dates are usually represented in terms of days.

To get hours from that you would multiply by 24 to get hours (then 60 for minutes, etc., etc.), not divide.

Using an Existing Function

A simple way to calculate time differences using a built in function is with DateDiffwhich has a signature of:

DateDiff( interval, date1, date2, [firstdayofweek], [firstweekofyear] )

There are plenty of intervals for you to choose from, ranging from year to second. Minutes is "n".

Ex:

DateDiff("n", "00:00", "12:00") '-> Result: 0.5

Documentation: msdn.microsoft.com/.../datediff-function

Simple Math Version

Even simpler is to just subtract:

abs(time2 - time1)

Where the result will be in days.

EDIT:

I would encourage you to make sure your functions do exactly what you expect them to do and not just hack something together that works but upon further changes breaks. It's poor coding practice and you learn nothing from it.

However, in an effort to give a response, a simple roundabout way to get a minimum cutoff of one hour would be to simply take the smallest value you would like to have, compare it to your value and choose the larger of the two.

calculatedValue <- SomeNumber
cutoffValue <- 1/24
chosenValue <- MAX(calculatedValue, cutoffValue)

推荐阅读