用Datediff不是好办法,Datediff计算年时即使相差1天(比如2004-12-31和2005-01-01)也算1年的。偶觉得还是用天来计算好些。 Dim l As Long Dim lNian As Long Dim lYue As Long Dim lDay As Long l = Date - DTPicker1.Value lNian = l \ 365 lYue = (l Mod 365) \ 12 lDay = (l Mod 365) Mod 12
dim date1 as date date1 = DateSerial(year(date), DTPicker1.month, DTPicker1.day) sui = DateDiff("yyyy", date1, DTPicker1.Value) yue = DateDiff("m", date1, date) tian = DateDiff("d", date1, date)
这个大概差不多了: Dim sui As Integer, yue As Integer, tian As Integer Dim date1 As Date date1 = DateSerial(Year(Date), DTPicker1.Month, DTPicker1.Day) sui = DateDiff("yyyy", DTPicker1.Value, date1) If date1 > Date Then sui = sui - 1 date1 = DateAdd("yyyy", -1, date1) yue = DateDiff("m", date1, Date) date1 = DateAdd("m", yue, date1) If date1 > Date Then yue = yue - 1 date1 = DateAdd("m", -1, date1) End If tian = DateDiff("d", date1, Date) Else yue = DateDiff("m", date1, Date) date1 = DateAdd("m", yue, date1) If date1 > Date Then yue = yue - 1 date1 = DateAdd("m", -1, date1) End If tian = DateDiff("d", date1, Date) End If 不知道对不对,汗~
viena,辛苦你了!谢谢 看起来感觉好复杂呀!
汗,简单测试了一下,还是有问题 如果给出的生日的那个月有31天, 那么30日和31日得到的天数是一样的程序写的也有问题 有一段完全一样的,完全可以放在if块的外面,这样: Dim sui As Integer, yue As Integer, tian As Integer'岁、月、天 Dim date1 As Date date1 = DateSerial(Year(Date), DTPicker1.Month, DTPicker1.Day) sui = DateDiff("yyyy", DTPicker1.Value, date1) If date1 > Date Then sui = sui - 1 date1 = DateAdd("yyyy", -1, date1) End If yue = DateDiff("m", date1, Date) date1 = DateAdd("m", yue, date1) If date1 > Date Then yue = yue - 1 date1 = DateAdd("m", -1, date1) End If tian = DateDiff("d", date1, Date)
加一句 if DTPicker1.Day = 30 then tian = tian + 1
晕,错了,应该是 if DateAdd("d",1,DTPicker1.Value) = 31 then tian = tian + 1
狂晕,还是不对,应该是 If Day(DateAdd("d", 1, DTPicker1.Value)) = 31 Then tian = tian + 1
viena真是个热心的人!赞! 楼主已经指出了我的那个写法中应该是30而不是12,另外我的算法肯定是不精确的,是个概数。没有仔细测试 viena 的算法,但从精确的角度,肯定是更合理。说一下闰年,我觉得不应该考虑为闰年加1,比如1999年1月1日跟2001年1月2日,虽然其间有一个闰年,但我们还是认为相差2年零1天,而不是2年零两天。另外有些东西本身就比较模糊,比如1999年4月30日跟2001年5月31日,你说应该差多少?如果说答案是2年1个月1天,那么再看1999年4月30日跟2001年6月1日是不是也应该是2年1个月1天,区间明明不同,结果为何一样?
dim iyear as integer,imon as integer, iday as integer dim dtmp as date dim smsg as stringdtmp = DTPicker1.value if date <= dtemp then msgbox "还没出生。开玩笑吧?" exit sub end ifiyear = datediff("y", dtmp, date) dtmp = dateadd("y", iyear, dtmp) '年对齐 imon = datediff("m", dtmp, date) dtmp = dateadd("m", imon, dtmp) '月对齐 iday = datediff("d", dtmp, date)smsg = "你的年龄是 " if iyear then smsg = smsg & iyear & " 年" if imon then smsg = smsg & " " & imon & " 个月零" if iday then smsg = smsg & " " & iday & " 天" end if else if iday then smsg = smsg & "零 " & iday & " 天" else smsg = "你整整 " & iyear & " 周岁。生日快乐!" end if end if else if imon then smsg = smsg & " " & imon & " 个月零" if iday then smsg = smsg & " " & iday & " 天" else smsg = "你的年龄是整整 " & imon & " 个月" end if else smsg = "你的年龄仅仅 " & iday & " 天" end if end if msgbox smsg
汗,原来的测试只是对当前日期的比较 如果改变当前日期,就不对了,最后加的一句 If Day(DateAdd("d", 1, DTPicker1.Value)) = 31 Then tian = tian + 1 应该改为: If Day(DateAdd("d", 1, DTPicker1.Value)) = 31 And Day(DateSerial(Year(Date), Month(Date), 31)) = 31 Then tian = tian + 1 If Day(DTPicker1.Value) = 31 And Day(DateSerial(Year(Date), Month(Date), 31)) = 1 Then tian = tian - 1这样: Dim sui As Integer, yue As Integer, tian As Integer '岁、月、天 Dim date1 As Date date1 = DateSerial(Year(Date), DTPicker1.Month, DTPicker1.Day) sui = DateDiff("yyyy", DTPicker1.Value, date1) If date1 > Date Then sui = sui - 1 date1 = DateAdd("yyyy", -1, date1) End If yue = DateDiff("m", date1, Date) date1 = DateAdd("m", yue, date1) If date1 > Date Then yue = yue - 1 date1 = DateAdd("m", -1, date1) End If tian = DateDiff("d", date1, Date) 'If Day(DateAdd("d", 1, DTPicker1.Value)) = 31 Then tian = tian + 1 If Day(DateAdd("d", 1, DTPicker1.Value)) = 31 And Day(DateSerial(Year(Date), Month(Date), 31)) = 31 Then tian = tian + 1 If Day(DTPicker1.Value) = 31 And Day(DateSerial(Year(Date), Month(Date), 31)) = 1 Then tian = tian - 1 MsgBox sui & "岁零" & yue & "月零" & tian & "天"
一点疏忽,更正: '...... if date <= dtmp then '****** msgbox "还没出生。开玩笑吧?" exit sub end ifiyear = datediff("yyyy", dtmp, date) '****** dtmp = dateadd("yyyy", iyear, dtmp) '年对齐 ****** '......
算法补充: dtmp = DTPicker1.Value If Date <= dtmp Then MsgBox "还没出生。开玩笑吧?" Exit Sub End IfIf DateDiff("d", dtmp, Date) > 365 Then iyear = DateDiff("yyyy", dtmp, Date) dtmp = DateAdd("yyyy", iyear, dtmp) '年对齐 End If If dtmp > Date Then dtmp = DateAdd("yyyy", -1, dtmp) iyear = iyear - 1 End If imon = DateDiff("m", dtmp, Date) dtmp = DateAdd("m", imon, dtmp) '月对齐 If dtmp > Date Then dtmp = DateAdd("m", -1, dtmp) imon = imon - 1 End If iday = DateDiff("d", dtmp, Date)
代码不是最重要的,关键是流程要清爽,明白地说明思路:dtmp = DTPicker1.Value If Date <= dtmp Then MsgBox "还没出生。开玩笑吧?" Exit Sub End IfIf DateDiff("d", dtmp, Date) > 365 Then '避免跨年度问题 iyear = DateDiff("yyyy", dtmp, Date) '得到相差的年 dtmp = DateAdd("yyyy", iyear, dtmp) '年对齐 End IfIf dtmp > Date Then '避免加年后月份大于当前月问题 dtmp = DateAdd("yyyy", -1, dtmp) iyear = iyear - 1 End Ifimon = DateDiff("m", dtmp, Date) dtmp = DateAdd("m", imon, dtmp) '月对齐If dtmp > Date Then '避免加月后日大于当前日问题 dtmp = DateAdd("m", -1, dtmp) imon = imon - 1 End If iday = DateDiff("d", dtmp, Date)'到这里已经得到了年、月、日,下面仅仅是用人话来说清楚。smsg = "你的年龄是 " if iyear then smsg = smsg & iyear & " 年" '如果年不为0, 输出年数 if imon then smsg = smsg & " " & imon & " 个月零" if iday then smsg = smsg & " " & iday & " 天" end if else if iday then smsg = smsg & "零 " & iday & " 天" else smsg = "你整整 " & iyear & " 周岁。生日快乐!" end if end if else if imon then smsg = smsg & " " & imon & " 个月零" if iday then smsg = smsg & " " & iday & " 天" else smsg = "你的年龄是整整 " & imon & " 个月" end if else smsg = "你的年龄仅仅 " & iday & " 天" end if end if msgbox smsg
注释一下我的代码的最后两句:'如果选择的日期是30日,而当前日期的月份有31天,天数加1 If Day(DateAdd("d", 1, DTPicker1.Value)) = 31 And Day(DateSerial(Year(Date), Month(Date), 31)) = 31 Then tian = tian + 1'如果选择的日期是31日,而当前日期的月份只有30天,天数减1 If Day(DTPicker1.Value) = 31 And Day(DateSerial(Year(Date), Month(Date), 31)) = 1 Then tian = tian - 1
>2004-10-31和2004-10-30 >结果是一样的,都是1个月零29天问题的实质是,当月份加减时,如果结果所得的月份最大日期小于原来的日期,就取此最大日期了。 DateAdd("m", 1, "2004-1-30") = 04-2-29 所以不仅仅是 31 日的问题。又改了: Dim iyear As Integer, imon As Integer, iday As Integer Dim dtmp As Date Dim smsg As Stringdtmp = DTPicker1.Value If Date <= dtmp Then MsgBox "还没出生。开玩笑吧?" Exit Sub End IfIf DateDiff("d", dtmp, Date) > 365 Then '避免跨年度问题 iyear = DateDiff("yyyy", dtmp, Date) '得到相差的年 dtmp = DateAdd("yyyy", iyear, dtmp) '年对齐 End IfIf dtmp > Date Then '避免加年后月份大于当前月问题 dtmp = DateAdd("yyyy", -1, dtmp) iyear = iyear - 1 End Ifimon = DateDiff("m", dtmp, Date) '这里把 DateAdd 消掉的日子补上了 dtmp = DateAdd("m", imon, dtmp) + Day(dtmp) - Day(DateAdd("m", imon, dtmp)) '月对齐If dtmp > Date Then '避免加月后日大于当前日问题 '还有这里 dtmp = DateAdd("m", -1, dtmp) + Day(dtmp) - Day(DateAdd("m", -1, dtmp)) imon = imon - 1 End If iday = DateDiff("d", dtmp, Date)'到这里已经得到了年、月、日,下面仅仅是用人话来说清楚。smsg = "你的年龄是 " If iyear Then smsg = smsg & iyear & " 年" '如果年不为0, 输出年数 If imon Then smsg = smsg & " " & imon & " 个月零" If iday Then smsg = smsg & " " & iday & " 天" End If Else If iday Then smsg = smsg & "零 " & iday & " 天" Else smsg = "你整整 " & iyear & " 周岁。生日快乐!" End If End If Else If imon Then smsg = smsg & " " & imon & " 个月零" If iday Then smsg = smsg & " " & iday & " 天" Else smsg = "你的年龄是整整 " & imon & " 个月" End If Else smsg = "你的年龄仅仅 " & iday & " 天" End If End If MsgBox smsg可见,每月日数不规则造成很多麻烦。
然后判断是否小于0,如果小于0,则月份加上12再减,天数加上30再减了。
嘿嘿,有点笨,再想想有没有好办法
结合DTPicker1.Value的值具体怎么写语句呀?
在“新建窗体”对话框中,单击“设计视图”,然后单击“确定”。
使用工具箱内的“文本框”工具,在窗体中添加两个未绑定文本框。
将两个文本框的“名称”属性分别设置为“生日”和“年龄”。
将“生日”文本框的“格式”属性设置为“短日期”。
将“年龄”文本框的“控件来源”属性设置为以下表达式:
=DateDiff("yyyy", [Birthdate], Now())+ Int( Format(now(), "mmdd") < Format( [Birthdate], "mmdd") )切换至“窗体”视图。
在“生日”文本框中,键入一个日期(年/月/日),然后按 TAB 键。
显示在“年龄”文本框中的数字就是从这个生日开始到当前日期所度过的年数。呵呵,这个简单,再sql中一样
Dim l As Long
Dim lNian As Long
Dim lYue As Long
Dim lDay As Long
l = Date - DTPicker1.Value
lNian = l \ 365
lYue = (l Mod 365) \ 12
lDay = (l Mod 365) Mod 12
date1 = DateSerial(year(date), DTPicker1.month, DTPicker1.day)
sui = DateDiff("yyyy", date1, DTPicker1.Value)
yue = DateDiff("m", date1, date)
tian = DateDiff("d", date1, date)
Dim sui As Integer, yue As Integer, tian As Integer
Dim date1 As Date
date1 = DateSerial(Year(Date), DTPicker1.Month, DTPicker1.Day)
sui = DateDiff("yyyy", DTPicker1.Value, date1)
If date1 > Date Then
sui = sui - 1
date1 = DateAdd("yyyy", -1, date1)
yue = DateDiff("m", date1, Date)
date1 = DateAdd("m", yue, date1)
If date1 > Date Then
yue = yue - 1
date1 = DateAdd("m", -1, date1)
End If
tian = DateDiff("d", date1, Date)
Else
yue = DateDiff("m", date1, Date)
date1 = DateAdd("m", yue, date1)
If date1 > Date Then
yue = yue - 1
date1 = DateAdd("m", -1, date1)
End If
tian = DateDiff("d", date1, Date)
End If
不知道对不对,汗~
看起来感觉好复杂呀!
如果给出的生日的那个月有31天,
那么30日和31日得到的天数是一样的程序写的也有问题
有一段完全一样的,完全可以放在if块的外面,这样:
Dim sui As Integer, yue As Integer, tian As Integer'岁、月、天
Dim date1 As Date
date1 = DateSerial(Year(Date), DTPicker1.Month, DTPicker1.Day)
sui = DateDiff("yyyy", DTPicker1.Value, date1)
If date1 > Date Then
sui = sui - 1
date1 = DateAdd("yyyy", -1, date1)
End If
yue = DateDiff("m", date1, Date)
date1 = DateAdd("m", yue, date1)
If date1 > Date Then
yue = yue - 1
date1 = DateAdd("m", -1, date1)
End If
tian = DateDiff("d", date1, Date)
if DTPicker1.Day = 30 then tian = tian + 1
if DateAdd("d",1,DTPicker1.Value) = 31 then tian = tian + 1
If Day(DateAdd("d", 1, DTPicker1.Value)) = 31 Then tian = tian + 1
楼主已经指出了我的那个写法中应该是30而不是12,另外我的算法肯定是不精确的,是个概数。没有仔细测试 viena 的算法,但从精确的角度,肯定是更合理。说一下闰年,我觉得不应该考虑为闰年加1,比如1999年1月1日跟2001年1月2日,虽然其间有一个闰年,但我们还是认为相差2年零1天,而不是2年零两天。另外有些东西本身就比较模糊,比如1999年4月30日跟2001年5月31日,你说应该差多少?如果说答案是2年1个月1天,那么再看1999年4月30日跟2001年6月1日是不是也应该是2年1个月1天,区间明明不同,结果为何一样?
从2000-2-29整3年,是到2003-2-28,还是到2003-3-1?毕竟2003年没有2月29日
我的是按到2003-3-1算的
DateSerial(2003, 2, 29)得到的是2003-3-1....
dim dtmp as date
dim smsg as stringdtmp = DTPicker1.value
if date <= dtemp then
msgbox "还没出生。开玩笑吧?"
exit sub
end ifiyear = datediff("y", dtmp, date)
dtmp = dateadd("y", iyear, dtmp) '年对齐
imon = datediff("m", dtmp, date)
dtmp = dateadd("m", imon, dtmp) '月对齐
iday = datediff("d", dtmp, date)smsg = "你的年龄是 "
if iyear then
smsg = smsg & iyear & " 年"
if imon then
smsg = smsg & " " & imon & " 个月零"
if iday then
smsg = smsg & " " & iday & " 天"
end if
else
if iday then
smsg = smsg & "零 " & iday & " 天"
else
smsg = "你整整 " & iyear & " 周岁。生日快乐!"
end if
end if
else
if imon then
smsg = smsg & " " & imon & " 个月零"
if iday then
smsg = smsg & " " & iday & " 天"
else
smsg = "你的年龄是整整 " & imon & " 个月"
end if
else
smsg = "你的年龄仅仅 " & iday & " 天"
end if
end if
msgbox smsg
如果改变当前日期,就不对了,最后加的一句
If Day(DateAdd("d", 1, DTPicker1.Value)) = 31 Then tian = tian + 1
应该改为:
If Day(DateAdd("d", 1, DTPicker1.Value)) = 31 And Day(DateSerial(Year(Date), Month(Date), 31)) = 31 Then tian = tian + 1
If Day(DTPicker1.Value) = 31 And Day(DateSerial(Year(Date), Month(Date), 31)) = 1 Then tian = tian - 1这样:
Dim sui As Integer, yue As Integer, tian As Integer '岁、月、天
Dim date1 As Date
date1 = DateSerial(Year(Date), DTPicker1.Month, DTPicker1.Day)
sui = DateDiff("yyyy", DTPicker1.Value, date1)
If date1 > Date Then
sui = sui - 1
date1 = DateAdd("yyyy", -1, date1)
End If
yue = DateDiff("m", date1, Date)
date1 = DateAdd("m", yue, date1)
If date1 > Date Then
yue = yue - 1
date1 = DateAdd("m", -1, date1)
End If
tian = DateDiff("d", date1, Date)
'If Day(DateAdd("d", 1, DTPicker1.Value)) = 31 Then tian = tian + 1
If Day(DateAdd("d", 1, DTPicker1.Value)) = 31 And Day(DateSerial(Year(Date), Month(Date), 31)) = 31 Then tian = tian + 1
If Day(DTPicker1.Value) = 31 And Day(DateSerial(Year(Date), Month(Date), 31)) = 1 Then tian = tian - 1
MsgBox sui & "岁零" & yue & "月零" & tian & "天"
'......
if date <= dtmp then '******
msgbox "还没出生。开玩笑吧?"
exit sub
end ifiyear = datediff("yyyy", dtmp, date) '******
dtmp = dateadd("yyyy", iyear, dtmp) '年对齐 ******
'......
错误,dtemp 改 dtmp当天,没错,说我没出生
往前一天2004-12-28,说我整整1周岁,狂晕~
再往前一月2004-11-27,说我整整32周岁,晕倒~
再往前一年2003-11-27,说我整整398周岁,晕死了~
iyear = datediff("yyyy", dtmp, date) '******
dtmp = dateadd("yyyy", iyear, dtmp) '年对齐 ******
后测试:
2004-11-30
1个月零-1天2004-10-31
1个月零-2天2004-10-31
1个月零-2天
dtmp = DTPicker1.Value
If Date <= dtmp Then
MsgBox "还没出生。开玩笑吧?"
Exit Sub
End IfIf DateDiff("d", dtmp, Date) > 365 Then
iyear = DateDiff("yyyy", dtmp, Date)
dtmp = DateAdd("yyyy", iyear, dtmp) '年对齐
End If
If dtmp > Date Then
dtmp = DateAdd("yyyy", -1, dtmp)
iyear = iyear - 1
End If
imon = DateDiff("m", dtmp, Date)
dtmp = DateAdd("m", imon, dtmp) '月对齐
If dtmp > Date Then
dtmp = DateAdd("m", -1, dtmp)
imon = imon - 1
End If
iday = DateDiff("d", dtmp, Date)
If Date <= dtmp Then
MsgBox "还没出生。开玩笑吧?"
Exit Sub
End IfIf DateDiff("d", dtmp, Date) > 365 Then '避免跨年度问题
iyear = DateDiff("yyyy", dtmp, Date) '得到相差的年
dtmp = DateAdd("yyyy", iyear, dtmp) '年对齐
End IfIf dtmp > Date Then '避免加年后月份大于当前月问题
dtmp = DateAdd("yyyy", -1, dtmp)
iyear = iyear - 1
End Ifimon = DateDiff("m", dtmp, Date)
dtmp = DateAdd("m", imon, dtmp) '月对齐If dtmp > Date Then '避免加月后日大于当前日问题
dtmp = DateAdd("m", -1, dtmp)
imon = imon - 1
End If
iday = DateDiff("d", dtmp, Date)'到这里已经得到了年、月、日,下面仅仅是用人话来说清楚。smsg = "你的年龄是 "
if iyear then
smsg = smsg & iyear & " 年" '如果年不为0, 输出年数
if imon then
smsg = smsg & " " & imon & " 个月零"
if iday then
smsg = smsg & " " & iday & " 天"
end if
else
if iday then
smsg = smsg & "零 " & iday & " 天"
else
smsg = "你整整 " & iyear & " 周岁。生日快乐!"
end if
end if
else
if imon then
smsg = smsg & " " & imon & " 个月零"
if iday then
smsg = smsg & " " & iday & " 天"
else
smsg = "你的年龄是整整 " & imon & " 个月"
end if
else
smsg = "你的年龄仅仅 " & iday & " 天"
end if
end if
msgbox smsg
If Day(DateAdd("d", 1, DTPicker1.Value)) = 31 And Day(DateSerial(Year(Date), Month(Date), 31)) = 31 Then tian = tian + 1'如果选择的日期是31日,而当前日期的月份只有30天,天数减1
If Day(DTPicker1.Value) = 31 And Day(DateSerial(Year(Date), Month(Date), 31)) = 1 Then tian = tian - 1
>结果是一样的,都是1个月零29天问题的实质是,当月份加减时,如果结果所得的月份最大日期小于原来的日期,就取此最大日期了。
DateAdd("m", 1, "2004-1-30") = 04-2-29 所以不仅仅是 31 日的问题。又改了:
Dim iyear As Integer, imon As Integer, iday As Integer
Dim dtmp As Date
Dim smsg As Stringdtmp = DTPicker1.Value
If Date <= dtmp Then
MsgBox "还没出生。开玩笑吧?"
Exit Sub
End IfIf DateDiff("d", dtmp, Date) > 365 Then '避免跨年度问题
iyear = DateDiff("yyyy", dtmp, Date) '得到相差的年
dtmp = DateAdd("yyyy", iyear, dtmp) '年对齐
End IfIf dtmp > Date Then '避免加年后月份大于当前月问题
dtmp = DateAdd("yyyy", -1, dtmp)
iyear = iyear - 1
End Ifimon = DateDiff("m", dtmp, Date)
'这里把 DateAdd 消掉的日子补上了
dtmp = DateAdd("m", imon, dtmp) + Day(dtmp) - Day(DateAdd("m", imon, dtmp)) '月对齐If dtmp > Date Then '避免加月后日大于当前日问题
'还有这里
dtmp = DateAdd("m", -1, dtmp) + Day(dtmp) - Day(DateAdd("m", -1, dtmp))
imon = imon - 1
End If
iday = DateDiff("d", dtmp, Date)'到这里已经得到了年、月、日,下面仅仅是用人话来说清楚。smsg = "你的年龄是 "
If iyear Then
smsg = smsg & iyear & " 年" '如果年不为0, 输出年数
If imon Then
smsg = smsg & " " & imon & " 个月零"
If iday Then
smsg = smsg & " " & iday & " 天"
End If
Else
If iday Then
smsg = smsg & "零 " & iday & " 天"
Else
smsg = "你整整 " & iyear & " 周岁。生日快乐!"
End If
End If
Else
If imon Then
smsg = smsg & " " & imon & " 个月零"
If iday Then
smsg = smsg & " " & iday & " 天"
Else
smsg = "你的年龄是整整 " & imon & " 个月"
End If
Else
smsg = "你的年龄仅仅 " & iday & " 天"
End If
End If
MsgBox smsg可见,每月日数不规则造成很多麻烦。
我还没太明白,懒的去想,没想到看似简单的一个小问题这么麻烦~