如何在VBA中找到日期之间的差异

I am trying to find out the difference between the system date and the date stored in the worksheet. If the difference between them is > 30 days, the result is true, else the result is false

我试图找出系统日期和工作表中存储的日期之间的差异。如果它们之间的差异> 30天，则结果为true，否则结果为false

Dim result as boolean
Dim sDate as string
sDate = Date
if Worksheets("dates").Cells(1,1) - sDate > 30 then 'how do I do this?
     result = true
else
     result = false
end if

How do I find out the difference in days between the system date and the date stored in the worksheet? The date in the worksheet can be a past date, too.

如何找出系统日期与工作表中存储的日期之间的天数差异？工作表中的日期也可以是过去的日期。

3 个解决方案

#1

I wonder why I rarely see people using the date functions.

我想知道为什么我很少看到人们使用日期功能。

You can also use this:

你也可以用这个：

if DateDiff("d", date1, date2) > 30 then

in this case, date1 would be CDate(Worksheets("dates").Cells(1,1)) and date2 would be sdate (either cast with CDate or dim'd as a date as Jeff said.

在这种情况下，date1将是CDate（工作表（“日期”）。单元格（1,1））和日期2将是sdate（使用CDate或dim'd作为日期，如杰夫所说。

"d" means we are getting the difference in days. Here are the intervals for years, months, etc. in VBA:

“d”表示我们在天数上有所不同。以下是VBA中年，月等的间隔：

yyyy - Year
q - Quarter
m - Month
y - Day of year
d - Day
w - Weekday
ww - Week
h - Hour
n - Minute
s - Second

#2

Try this:

尝试这个：

if CDate(Worksheets("dates").Cells(1,1)) - sDate > 30 then

#3

sDate is a STRING, which is NOT a Real Date!

sDate是一个STRING，这不是一个真实的日期！

Convert your string to a date, using either the CDate() function or the DateValue() function.

使用CDate（）函数或DateValue（）函数将字符串转换为日期。

However, there is a caveat in this kind of conversion. These conversion will handle the following structures:

但是，这种转换有一个警告。这些转换将处理以下结构：

yyyy/mm/dd
yyyy/m/d
mm/dd/yyyy
m/d/yyyy

These will not be correctly converted

这些将无法正确转换

dd/mm/yyyy
d/m/yyyy

And avoid using any 2-digit year.

并避免使用任何2位数的年份。

I would advise using the DateSerial() function for date conversion.

我建议使用DateSerial（）函数进行日期转换。

So regarding your code, assuming that the values on yor sheet are actually dates (to be certain, simply select the column and change the Number Format to GENERAL. If they are real dates, each will display a PURE NUMBER. Remember to hit UNDO to get your Date Format back)

所以关于你的代码，假设yor表上的值实际上是日期（确切地说，只需选择列并将数字格式更改为GENERAL。如果它们是实际日期，则每个都将显示一个纯数字。记得点击UNDO到得到你的日期格式）

Dim result As Boolean

If Worksheets("dates").Cells(1, 1).Value - Date > 30 Then
     result = True
Else
     result = False
End If

#1